Terminal Terminal | Web Web
Home  //  Play

Manticore Index Administration

Difficulty: Beginner
Estimated Time: 10 minutes

Manticoresearch - RT mode - index administration

In this course we learn how to manipulate indexes in Manticore Search using SQL statements.

Manticore Index Administration

Step 1 of 4

Index creation

Connect first to Manticore via SQL:

mysql -P9306 -h0

A simple table creation statement looks like:

CREATE TABLE testrt1 (title TEXT,category INT);

This will create an index having the document id (which doesn't need to be declared), a text field and a integer attribute.

Indexes support a range of options that can be passed at the declaration:

For example

CREATE TABLE testrt2 (title TEXT, category INT) html_strip='1' docstore_compression_level = '12';

This index has infixing enabled, text will be stripped of HTML and the compression level for document storage is set at the highest level.

If the type is not specified, by default the index will be a Real-Time. If we want to create a distributed index, we need to pass the type:

CREATE TABLE testrtdist type='distributed' local='testrt1' local='testrt2';

If we are not sure if the table is already made or not, the 'IF NOT EXISTS' clause can be used to not end in error:

CREATE TABLE IF NOT EXISTS testrt1 (title TEXT,category INT);

It is also possible to create an index using the settings and schema of an index that already exists:

CREATE TABLE testrt3 LIKE testrt2;

More table operations

To delete an index simply:

DROP TABLE testrt3;

To empty an index:

TRUNCATE TABLE testrt2;

To add or drop a column:

ALTER TABLE testrt2 ADD COLUMN tagId INT;

ALTER TABLE testrt2 DROP COLUMN tagId;

Please note that TEXT cannot be added or dropped currently. You will need to re-create the table if you need to modify the TEXT fields.

It is also possible to change index settings:

ALTER TABLE testrt2 ignore_chars='.';

Please note that tokenization settings will not apply to existing data in the full-text component, only new documents added after the ALTER will get affected. If you need to affect the entire collection, the index must be re-created with the desired settings.

Table information

There are several statements that provide information about an index.

To get the index schema:

DESCRIBE testrt2;

Information about number of documents, size and performance metrics can be seen with SHOW:

SHOW INDEX testrt2 STATUS;

SHOW can also provide the list of settings:

SHOW INDEX testrt2 SETTINGS;

If you need to export the index declaration, use SHOW CREATE:

SHOW CREATE TABLE testrt2;

Importing an index

RT indexes can be imported using IMPORT TABLE statement. The index files will be copied in the data_dir.

Example:

IMPORT TABLE movies FROM '/index/movies_rt';

SELECT * FROM movies\G