We recently posted an article about the new Real-Time mode. In this one, we will take a detailed look on how it works and how you can use it on your own. We’ll learn how to manipulate indexes in Manticore Search using SQL statements.
Index creation
Let’s first connect to Manticore via SQL:
root@rtmode-79f9d6d867-fptcc:/# mysql -P9306 -h0
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MySQL connection id is 1
Server version: 3.4.0 0686d9f0@200326 release
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
A simple table creation statement looks like:
MySQL [(none)]> 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 an integer attribute.
Indexes support a range of options that can be passed at the declaration:
For example:
MySQL [(none)]> CREATE TABLE testrt2 (title TEXT, category INT) html_strip='1' docstore_compression_level = '12';
In this index the 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 real-time. If we want to create a distributed index, we need to pass the type:
MySQL [(none)]> 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 avoid an error:
MySQL [(none)]> 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:
MySQL [(none)]> CREATE TABLE testrt3 LIKE testrt2;
More table operations
To delete an index use DROP TABLE:
MySQL [(none)]> DROP TABLE testrt3;
To empty an index:
MySQL [(none)]> TRUNCATE TABLE testrt2;
To add or drop a column:
MySQL [(none)]> ALTER TABLE testrt2 ADD COLUMN tagId INT;
MySQL [(none)]> ALTER TABLE testrt2 DROP COLUMN tagId;
Please note that TEXT fields cannot be added or dropped currently. You will need to re-create the table if you need to modify TEXT fields.
It is also possible to change index settings:
MySQL [(none)]> 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 update 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 index schema:
MySQL [(none)]> DESCRIBE testrt2;
+----------+--------+----------------+
| Field | Type | Properties |
+----------+--------+----------------+
| id | bigint | |
| title | field | indexed stored |
| category | uint | |
+----------+--------+----------------+
Information about number of documents, size, and performance metrics can be seen with SHOW INDEX STATUS:
MySQL [(none)]> SHOW INDEX testrt2 STATUS;
+-------------------+--------------------------------------------------------------------------+
| Variable_name | Value |
+-------------------+--------------------------------------------------------------------------+
| index_type | rt |
| indexed_documents | 0 |
| indexed_bytes | 0 |
| ram_bytes | 5840 |
| disk_bytes | 331 |
| ram_chunk | 0 |
| ram_chunks_count | 0 |
| disk_chunks | 0 |
| mem_limit | 134217728 |
| ram_bytes_retired | 0 |
| tid | 0 |
| tid_saved | 0 |
| query_time_1min | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| query_time_5min | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| query_time_15min | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| query_time_total | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| found_rows_1min | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| found_rows_5min | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| found_rows_15min | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
| found_rows_total | {"queries":0, "avg":"-", "min":"-", "max":"-", "pct95":"-", "pct99":"-"} |
+-------------------+--------------------------------------------------------------------------+
SHOW INDEX SETTINGS can also provide a list of index settings:
MySQL [(none)]> SHOW INDEX testrt2 SETTINGS;
+---------------+----------------------------------------------------------+
| Variable_name | Value |
+---------------+----------------------------------------------------------+
| settings | html_strip = 1 |
| | charset_table = non_cjk |
| | ignore_chars = . |
+---------------+----------------------------------------------------------+
If you need to export the full index declaration, use SHOW CREATE TABLE:
MySQL [(none)]> SHOW CREATE TABLE testrt2;
+---------+-------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------------------------------------------------------------------------------------------+
| testrt2 | CREATE TABLE testrt2 (category integer,title text indexed stored) html_strip='1' charset_table='non_cjk' ignore_chars='.' |
+---------+-------------------------------------------------------------------------------------------------------------------------------+
Importing an index
RT indexes can be imported using the IMPORT TABLE statement.
The index files will be copied to your data_dir specified in the configuration.
Example:
MySQL [(none)]> IMPORT TABLE movies FROM '/index/movies_rt';
MySQL [(none)]> SELECT * FROM moviesG
*************************** 1. row ***************************
id: 1
num_critic_for_reviews: 4
duration: 96
director_facebook_likes: 0
actor_3_facebook_likes: 460
actor_1_facebook_likes: 708
gross: 0
num_voted_users: 961
cast_total_facebook_likes: 2307
facenumber_in_poster: 0
num_user_for_reviews: 12
budget: 3500000
title_year: 2009
actor_2_facebook_likes: 574
movie_facebook_likes: 211
imdb_score: 4.800000
aspect_ration: 2.350000
color: Color
movie_imdb_link: http://www.imdb.com/title/tt1002561/?ref_=fn_tt_tt_1
language: English
country: USA
content_rating:
director_name: Charles Adelman
actor_2_name: Kevin Pollak
actor_1_name: Teri Polo
movie_title: 02:13
actor_3_name: Jere Burns
plot_keywords: death|forensic|murder|profiler|serial killer
...
*************************** 20. row ***************************
id: 20
num_critic_for_reviews: 1
duration: 111
director_facebook_likes: 0
actor_3_facebook_likes: 247
actor_1_facebook_likes: 1000
gross: 14616
num_voted_users: 314
cast_total_facebook_likes: 2059
facenumber_in_poster: 1
num_user_for_reviews: 10
budget: 12000000
title_year: 2015
actor_2_facebook_likes: 445
movie_facebook_likes: 26000
imdb_score: 7.500000
aspect_ration: 1.850000
color: Color
movie_imdb_link: http://www.imdb.com/title/tt3453052/?ref_=fn_tt_tt_1
language: English
country: USA
content_rating: R
director_name: Timothy Hines
actor_2_name: Kelly LeBrock
actor_1_name: Christopher Lambert
movie_title: 10 Days in a Madhouse
actor_3_name: Alexandra Callas
plot_keywords:
We believe that the new RT mode which is a new default mode will make it easier to maintain indexes in Manticore Search in most cases. For the rest the plain mode is still supported. You can learn about the differences from our documentation.
Interactive Course
You can learn more about our Real-time index administration in our “ManticoreSearch - RT mode - index administration” interactive course which features a command line for easier learning.