Emptying an index

Manticore Search has a single level of hierarchy of indexes.

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.

While the data collections in Manticore are called indexes, the statement that displays them is SHOW TABLES for compatibility with miscellaneous SQL clients.

General syntax:

SHOW TABLES [ LIKE pattern ]

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

‹›

SQL
PHP
Python
javascript
Java

📋

SHOW TABLES;

$client->nodes()->table();

utilsApi.sql('mode=raw&query=SHOW TABLES')

res = await utilsApi.sql('mode=raw&query=SHOW TABLES');

utilsApi.sql("mode=raw&query=SHOW TABLES")

‹›

Response

+----------+-------------+
| Index    | Type        |
+----------+-------------+
| dist1    | distributed |
| rt       | rt          |
| products | rt       |
+----------+-------------+
4 rows in set (0.00 sec)

Array
(
    [dist1] => distributed
    [rt] => rt
    [products] => rt
)

{u'columns': [{u'Index': {u'type': u'string'}},
              {u'Type': {u'type': u'string'}}],
 u'data': [{u'Index': u'dist1', u'Type': u'distributed'},
           {u'Index': u'rt', u'Type': u'rt'},
           {u'Index': u'products', u'Type': u'rt'}],
 u'error': u'',
 u'total': 0,
 u'warning': u''}

{"columns":[{"Index":{"type":"string"}},{"Type":{"type":"string"}}],"data":[{"Index":"products","Type":"rt"}],"total":0,"error":"","warning":""}

{columns=[{Index={type=string}}, {Type={type=string}}], data=[{Index=products, Type=rt}], total=0, error=, warning=}

Optional LIKE clause is supported for filtering indexes by name.

‹›

SQL
PHP
Python
javascript
Java

📋

SHOW TABLES LIKE 'pro%';

$client->nodes()->table(['body'=>['pattern'=>'pro%']]);

res = await utilsApi.sql('mode=raw&query=SHOW TABLES LIKE \'pro%\'');

utilsApi.sql('mode=raw&query=SHOW TABLES LIKE \'pro%\'')

utilsApi.sql("mode=raw&query=SHOW TABLES LIKE 'pro%'")

‹›

Response

+----------+-------------+
| Index    | Type        |
+----------+-------------+
| products | distributed |
+----------+-------------+
1 row in set (0.00 sec)

Array
(
    [products] => distributed
)

{u'columns': [{u'Index': {u'type': u'string'}},
              {u'Type': {u'type': u'string'}}],
 u'data': [{u'Index': u'products', u'Type': u'rt'}],
 u'error': u'',
 u'total': 0,
 u'warning': u''}

{"columns":[{"Index":{"type":"string"}},{"Type":{"type":"string"}}],"data":[{"Index":"products","Type":"rt"}],"total":0,"error":"","warning":""}

{columns=[{Index={type=string}}, {Type={type=string}}], data=[{Index=products, Type=rt}], total=0, error=, warning=}

{DESC | DESCRIBE} index [ LIKE pattern ]

DESCRIBE statement lists index 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.

SHOW CREATE TABLE name

Prints the CREATE TABLE statement that creates the named table.

‹›

SQL

SQL

📋

SHOW CREATE TABLE idx\G

‹›

Response

       Table: idx
Create Table: CREATE TABLE idx (
f text indexed stored
) charset_table='non_cjk,cjk' morphology='icu_chinese'
1 row in set (0.00 sec)

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

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 index 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)

️ Deleting an index

Deleting an index is performed in 2 steps:

Index is cleared (similar to TRUNCATE)
All index files are removed from the index folder. All the external index files that were used by the index (such as wordforms, extensions or stopwords) are also deleted. Note that these external files are copied to index folder when CREATE TABLE is used, so the original files specified in CREATE TABLE will not be deleted.

Deleting an index is possible only when the server is running in RT mode. It is possible to delete RT indexes, PQ indexes and distributed indexes.

‹›

SQL
HTTP
PHP
Python
javascript
Java

📋

DROP TABLE products;

POST /sql -d "mode=raw&query=DROP TABLE products"

$params = [ 'index' => 'products' ];

$response = $client->indices()->drop($params);

utilsApi.sql('mode=raw&query=DROP TABLE products')

res = await utilsApi.sql('mode=raw&query=DROP TABLE products');

sqlresult = utilsApi.sql("mode=raw&query=DROP TABLE products");

‹›

Response

Query OK, 0 rows affected (0.02 sec)

{
"total":0,
"error":"",
"warning":""
}

Array
(
    [total] => 0
    [error] =>
    [warning] =>
)

{u'error': u'', u'total': 0, u'warning': u''}

{"total":0,"error":"","warning":""}

{total=0, error=, warning=}

Here is the syntax of the DROP TABLE statement in SQL:

DROP TABLE [IF EXISTS] index_name

When deleting an index via SQL, adding IF EXISTS can be used to delete the index only if it exists. If you try to delete a non-existing index with the IF EXISTS option, nothing happens.

When deleting an index via PHP, you can add an optional silent parameter which works the same as IF EXISTS.

‹›

SQL
HTTP
PHP
Python
javascript
Java

📋

DROP TABLE IF EXISTS products;

POST /sql -d "mode=raw&query=DROP TABLE IF EXISTS products"

$params =
[
  'index' => 'products',
  'body' => ['silent' => true]
];

$client->indices()->drop($params);

utilsApi.sql('mode=raw&query=DROP TABLE IF EXISTS products')

res = await utilsApi.sql('mode=raw&query=DROP TABLE IF EXISTS products');

sqlresult = utilsApi.sql("mode=raw&query=DROP TABLE IF EXISTS products");

‹›

Response

{u'error': u'', u'total': 0, u'warning': u''}

{"total":0,"error":"","warning":""}

{total=0, error=, warning=}

️ Listing indexes ️ Emptying an index

The index can be emptied with a TRUNCATE TABLE SQL statement or with a truncate() PHP client function.

Here is the syntax for the SQL statement:

TRUNCATE TABLE index_name [WITH RECONFIGURE]

When this statement is executed, it clears the RT index completely. It disposes the in-memory data, unlinks all the index data files, and releases the associated binary logs.

An index can also be emptied with DELETE FROM index WHERE id>0, but it's not recommended as it's much slower than TRUNCATE.

‹›

SQL
HTTP
PHP
Python
javascript
Java

📋

TRUNCATE TABLE products;

POST /sql -d "mode=raw&query=TRUNCATE TABLE products"

$params = [ 'index' => 'products' ];
$response = $client->indices()->truncate($params);

utilsApi.sql('mode=raw&query=TRUNCATE TABLE products')

res = await utilsApi.sql('mode=raw&query=TRUNCATE TABLE products');

utilsApi.sql("mode=raw&query=TRUNCATE TABLE products");

‹›

Response

Query OK, 0 rows affected (0.02 sec)

{
"total":0,
"error":"",
"warning":""
}

Array(
    [total] => 0
    [error] => 
    [warning] => 
)

{u'error': u'', u'total': 0, u'warning': u''}

{"total":0,"error":"","warning":""}

{total=0, error=, warning=}

One of the possible uses of this command is before attaching an index.

When RECONFIGURE option is used new tokenization, morphology, and other text processing settings specified in the config take effect after the index gets cleared. With this option clearing and reconfiguring an index becomes one atomic operation.

‹›

SQL
HTTP
PHP
Python
javascript
Java

📋

TRUNCATE TABLE products with reconfigure;

POST /sql -d "mode=raw&query=TRUNCATE TABLE products with reconfigure"

$params = [ 'index' => 'products', 'with' => 'reconfigure' ];
$response = $client->indices()->truncate($params);

utilsApi.sql('mode=raw&query=TRUNCATE TABLE products WITH RECONFIGURE')

res = await utilsApi.sql('mode=raw&query=TRUNCATE TABLE products WITH RECONFIGURE');

utilsApi.sql("mode=raw&query=TRUNCATE TABLE products WITH RECONFIGURE");

‹›

Response

Query OK, 0 rows affected (0.02 sec)

{
"total":0,
"error":"",
"warning":""
}

Array(
    [total] => 0
    [error] => 
    [warning] => 
)

{u'error': u'', u'total': 0, u'warning': u''}

{"total":0,"error":"","warning":""}

{total=0, error=, warning=}

️ Deleting an index ️ Creating a cluster

Manticore Search is a highly distributed system and consists of all the needed components to allow you build a highly available and scalable setup of a database for search:

distributed index allowing sharding
- Mirroring for high availability
- Load balancing for high scalability
Replication for data safety

Manticore Search is extremely flexible in terms of how you setup your cluster, there's no limitations and it's up to you how you design it. Just learn the tools mentioned above and use them to achieve your goal.

️ Emptying an index Adding a new node

Listing indexes

SHOW TABLES

DESCRIBE

SHOW CREATE TABLE

Percolate index schemas

Deleting an index

Manticore cluster