# Manticore Search - RT mode - index administration Definitive Guide We recently posted an article [about the new Real-Time mode](https://manticoresearch.com/blog/rt-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: ```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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql MySQL [(none)]> CREATE TABLE testrt3 LIKE testrt2; ``` # More table operations --- To delete an index use DROP TABLE: ```sql MySQL [(none)]> DROP TABLE testrt3; ``` To empty an index: ```sql MySQL [(none)]> TRUNCATE TABLE testrt2; ``` To add or drop a column: ```sql MySQL [(none)]> ALTER TABLE testrt2 ADD COLUMN tagId INT; ``` ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql MySQL [(none)]> IMPORT TABLE movies FROM '/index/movies_rt'; ``` ```sql 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](https://docs.manticoresearch.com/latest/html/getting-started/configuration.html). [![img](./manticoresearch-rt-mode-index-administration-definitive-guide/RT-mode-index.gif)](https://play.manticoresearch.com/rtmode/) # [Interactive Course](https://play.manticoresearch.com/rtmode/) 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.