Listing tables

Manticore Search has a single level of hierarchy of tables.

There is no concept of grouping tables in databases like in other DBMS. Still, Manticore accepts SHOW DATABASES statements for interoperability with SQL dialect, but the statement doesn't return anything.

SHOW TABLES

General syntax:

SHOW TABLES [ LIKE pattern ]

SHOW TABLES statement enumerates all currently active tables along with their types. Existing table types are local, distributed, rt, percolate and template.

‹›
  • SQL
  • PHP
  • Python
  • javascript
  • Java
📋
SHOW TABLES;
‹›
Response
+----------+-------------+
| Index    | Type        |
+----------+-------------+
| dist     | distributed |
| plain    | local       |
| pq       | percolate   |
| rt       | rt          |
| template | template    |
+----------+-------------+
5 rows in set (0.00 sec)

Optional LIKE clause is supported for filtering tables by name.

‹›
  • SQL
  • PHP
  • Python
  • javascript
  • Java
📋
SHOW TABLES LIKE 'pro%';
‹›
Response
+----------+-------------+
| Index    | Type        |
+----------+-------------+
| products | distributed |
+----------+-------------+
1 row in set (0.00 sec)

DESCRIBE

{DESC | DESCRIBE} table [ LIKE pattern ]

DESCRIBE statement lists table columns and their associated types. Columns are document ID, full-text fields, and attributes. The order matches that in which fields and attributes are expected by INSERT and REPLACE statements. Column types are field, integer, timestamp, ordinal, bool, float, bigint, string, and mva. ID column will be typed as bigint. Example:

mysql> DESC rt;
+---------+---------+
| Field   | Type    |
+---------+---------+
| id      | bigint  |
| title   | field   |
| content | field   |
| gid     | integer |
+---------+---------+
4 rows in set (0.00 sec)

An optional LIKE clause is supported. Refer to SHOW META for its syntax details.

SELECT FROM name.table

You can also see table schema by executing the query select * from <table_name>.table. The benefit of this method is that you can use WHERE for filtering:

‹›
  • SQL
SQL
📋
select * from tbl.table where type='text';
‹›
Response
+------+-------+------+----------------+
| id   | field | type | properties     |
+------+-------+------+----------------+
|    2 | title | text | indexed stored |
+------+-------+------+----------------+
1 row in set (0.00 sec)

You can also do many other things, consider <your_table_name>.table just a regular Manticore table where the columns are integer and string attributes.

‹›
  • SQL
SQL
📋
select field from tbl.table;
select field, properties from tbl.table where type in ('text', 'uint');
select * from tbl.table where properties any ('stored');

SHOW CREATE TABLE

SHOW CREATE TABLE name

Prints the CREATE TABLE statement that creates the named table.

‹›
  • SQL
SQL
📋
SHOW CREATE TABLE tbl\G
‹›
Response
       Table: tbl
Create Table: CREATE TABLE tbl (
f text indexed stored
) charset_table='non_cjk,cjk' morphology='icu_chinese'
1 row in set (0.00 sec)

Percolate table schemas

If you apply DESC statement to a percolate table it will show the outer table schema, i.e. the schema of stored queries. It's static and the same for all local percolate tables:

mysql> DESC pq;
+---------+--------+
| Field   | Type   |
+---------+--------+
| id      | bigint |
| query   | string |
| tags    | string |
| filters | string |
+---------+--------+
4 rows in set (0.00 sec)

If you're looking for an expected document schema use DESC <pq table name> table:

mysql> DESC pq TABLE;
+-------+--------+
| Field | Type   |
+-------+--------+
| id    | bigint |
| title | text   |
| gid   | uint   |
+-------+--------+
3 rows in set (0.00 sec)

Also desc pq table like ... is supported and works as follows:

mysql> desc pq table like '%title%';
+-------+------+----------------+
| Field | Type | Properties     |
+-------+------+----------------+
| title | text | indexed stored |
+-------+------+----------------+
1 row in set (0.00 sec)
Remote tables ️ Deleting a table