⪢ Table settings and status

SHOW TABLE INDEXES

The SHOW TABLE INDEXES SQL statement displays the secondary indexes available for a specified table, along with their properties. Secondary indexes improve query performance by creating additional data structures that speed up searches on specific columns.

The syntax is:

SHOW TABLE table_name INDEXES

The displayed properties include the following columns:

  • Name: The name of the secondary index. Can be used in query optimizer hints.
  • Type: The type of data stored in the secondary index. For plain attributes, it matches the type of the original attribute. For secondary indexes generated from JSON attributes, the type is deduced by scanning all documents and determining the types of all JSON properties.
  • Enabled: Indicates whether the index is currently enabled and can be used to improve search speed. When an attribute is updated, the secondary index for that attribute is temporarily disabled until the index is rebuilt. You can rebuild disabled indexes using the ALTER TABLE ... REBUILD SECONDARY command.
  • Percent: In an RT table, different disk chunks may contain different secondary indexes especially when JSON attributes are used. This percentage shows how many chunks have an index with the same name, type, and enabled state.
‹›
  • SQL
SQL
📋
SHOW TABLE test INDEXES;
‹›
Response
+------------------------------+--------+---------+---------+
| Name                         | Type   | Enabled | Percent |
+------------------------------+--------+---------+---------+
| j['addresses']               | uint32 | 1       | 100     |
| j['addresses']['a1']         | uint32 | 1       | 100     |
| j['addresses']['a2']         | uint32 | 1       | 100     |
| j['addresses']['a3']         | uint32 | 1       | 100     |
| j['addresses']['a4']         | uint32 | 1       | 100     |
| j['addresses']['a5']         | uint32 | 1       | 100     |
| j['addresses']['a6']         | uint32 | 1       | 100     |
| j['factor']                  | uint32 | 1       | 100     |
| j['int_arr']                 | uint32 | 1       | 100     |
| j['tags']                    | uint32 | 1       | 100     |
| id                           | int64  | 1       | 100     |
| j['price']                   | float  | 1       | 100     |
| j['addresses']['a1']['id']   | string | 1       | 100     |
| j['addresses']['a1']['name'] | string | 1       | 100     |
| j['addresses']['a2']['id']   | string | 1       | 100     |
| j['addresses']['a2']['name'] | string | 1       | 100     |
| j['addresses']['a3']['id']   | string | 1       | 100     |
| j['addresses']['a3']['name'] | string | 1       | 100     |
| j['addresses']['a4']['id']   | string | 1       | 100     |
| j['addresses']['a4']['name'] | string | 1       | 100     |
| j['addresses']['a5']['id']   | string | 1       | 100     |
| j['addresses']['a5']['name'] | string | 1       | 100     |
| j['addresses']['a6']['id']   | string | 1       | 100     |
| j['addresses']['a6']['name'] | string | 1       | 100     |
| j['arr']                     | string | 1       | 100     |
| j['str']                     | string | 1       | 100     |
| j['tags']['1']               | string | 1       | 100     |
| j['tags']['2']               | string | 1       | 100     |
| j['tags']['3']               | string | 1       | 100     |
+------------------------------+--------+---------+---------+
29 rows in set (0.00 sec)

SHOW TABLE STATUS

SHOW TABLE STATUS is an SQL statement that displays various per-table statistics.

The syntax is:

SHOW TABLE table_name STATUS

Depending on index type, displayed statistic includes different set of rows:

  • template: index_type.
  • distributed: index_type, query_time_1min, query_time_5min,query_time_15min,query_time_total, exact_query_time_1min, exact_query_time_5min, exact_query_time_15min, exact_query_time_total, found_rows_1min, found_rows_5min, found_rows_15min, found_rows_total.
  • percolate: index_type, stored_queries, ram_bytes, disk_bytes, max_stack_need, average_stack_base, desired_thread_stack, tid, tid_saved, query_time_1min, query_time_5min,query_time_15min,query_time_total, exact_query_time_1min, exact_query_time_5min, exact_query_time_15min, exact_query_time_total, found_rows_1min, found_rows_5min, found_rows_15min, found_rows_total.
  • plain: index_type, indexed_documents, indexed_bytes, may be set of field_tokens_* and total_tokens, ram_bytes, disk_bytes, disk_mapped, disk_mapped_cached, disk_mapped_doclists, disk_mapped_cached_doclists, disk_mapped_hitlists, disk_mapped_cached_hitlists, killed_documents, killed_rate, query_time_1min, query_time_5min,query_time_15min,query_time_total, exact_query_time_1min, exact_query_time_5min, exact_query_time_15min, exact_query_time_total, found_rows_1min, found_rows_5min, found_rows_15min, found_rows_total.
  • rt: index_type, indexed_documents, indexed_bytes, may be set of field_tokens_* and total_tokens, ram_bytes, disk_bytes, disk_mapped, disk_mapped_cached, disk_mapped_doclists, disk_mapped_cached_doclists, disk_mapped_hitlists, disk_mapped_cached_hitlists, killed_documents, killed_rate, ram_chunk, ram_chunk_segments_count, disk_chunks, mem_limit, mem_limit_rate, ram_bytes_retired, optimizing, locked, tid, tid_saved, query_time_1min, query_time_5min,query_time_15min,query_time_total, exact_query_time_1min, exact_query_time_5min, exact_query_time_15min, exact_query_time_total, found_rows_1min, found_rows_5min, found_rows_15min, found_rows_total.

Here is the meaning of these values:

  • index_type: currently one of disk, rt, percolate, template, and distributed.
  • indexed_documents: number of indexed documents.
  • indexed_bytes: overall size of indexed text. Notice, this value is not strict, since in full-text index that is impossible to strictly recover back stored text to measure it.
  • stored_queries: number of percolate queries, stored in the table.
  • field_tokens_XXX: optional, total per-field lengths (in tokens) across the entire table (used internally for BM25A and BM25F ranking functions). Only available for tables built with index_field_lengths=1.
  • total_tokens: optional, overall sum of all field_tokens_XXX.
  • ram_bytes: total RAM occupied by table.
  • disk_bytes: total disk space, occupied by table.
  • disk_mapped: total size of file mappings.
  • disk_mapped_cached: total size of file mappings actually cached in RAM.
  • disk_mapped_doclists and disk_mapped_cached_doclists: portion of total and cached mappings belonging to document lists.
  • disk_mapped_hitlists and disk_mapped_cached_hitlists: portion of total and cached mappings belonging to hit lists. Doclists and hitlists values are shown separately since they're typically large (e.g., about 90% of the whole table's size).
  • killed_documents and killed_rate: the first indicates the number of deleted documents and the rate of deleted/indexed. Technically, deleting a document means suppressing it in search output, but it still physically exists in the table and will only be purged after merging/optimizing the table.
  • ram_chunk: size of the RAM chunk of real-time or percolate table.
  • ram_chunk_segments_count: RAM chunk is internally composed of segments, typically no more than 32. This line shows the current count.
  • disk_chunks: number of disk chunks in the real-time table.
  • mem_limit: actual value of rt_mem_limit for the table.
  • mem_limit_rate: the rate at which the RAM chunk will be flushed as a disk chunk, e.g., if rt_mem_limit is 128M and the rate is 50%, a new disk chunk will be saved when the RAM chunk exceeds 64M.
  • ram_bytes_retired: represents the size of garbage in RAM chunks (e.g., deleted or replaced documents not yet permanently removed).
  • optimizing: a value greater than 0 indicates that the table is currently performing optimization (i.e. it is merging some disk chunks right now).
  • locked: a value greater than 0 indicates that the table is currently locked by FREEZE. The number represents how many times the table has been frozen. For instance, a table might be frozen by manticore-backup and then frozen again by replication. It should only be completely unfrozen when no other process requires it to be frozen.
  • max_stack_need: stack space we need to calculate most complex from the stored percolate queries. That is dynamic value, depends on build details as compiler, optimization, hardware, etc.
  • average_stack_base: stack space which is usually occupied on start of calculation of percolate query.
  • desired_thread_stack: sum of above values, rounded up to 128 bytes edge. If this value is greater than thread_stack, you may not execute call pq over this table, as some stored queries will fail. Default thread_stack value is 1M (which is 1048576); other values should be configured.
  • tid and tid_saved: represent the state of saving the table. tid increases with each change (transaction). tid_saved shows the max tid of the state saved in a RAM chunk in <table>.ram file. When the numbers differ, some changes exist only in RAM and are also backed by binlog (if enabled). Performing FLUSH TABLE or scheduling periodic flushing saves these changes. After flushing, the binlog is cleared, and tid_saved represents the new actual state.
  • query_time_*, exact_query_time_*: query execution time statistics for the last 1 minute, 5 minutes, 15 minutes, and total since server start; data is encapsulated as a JSON object, including the number of queries and min, max, avg, 95, and 99 percentile values.
  • found_rows_*: statistics of rows found by queries; provided for the last 1 minute, 5 minutes, 15 minutes, and total since server start; data is encapsulated as a JSON object, including the number of queries and min, max, avg, 95, and 99 percentile values.
‹›
  • SQL
  • PHP
  • Python
  • Javascript
  • Java
  • C#
  • TypeScript
  • Go
📋
mysql> SHOW TABLE statistic STATUS;
‹›
Response
+-----------------------------+--------------------------------------------------------------------------+
| Variable_name               | Value                                                                    |
+-----------------------------+--------------------------------------------------------------------------+
| index_type                  | rt                                                                       |
| indexed_documents           | 146000                                                                   |
| indexed_bytes               | 149504000                                                                |
| ram_bytes                   | 87674788                                                                 |
| disk_bytes                  | 1762811                                                                  |
| disk_mapped                 | 794147                                                                   |
| disk_mapped_cached          | 802816                                                                   |
| disk_mapped_doclists        | 0                                                                        |
| disk_mapped_cached_doclists | 0                                                                        |
| disk_mapped_hitlists        | 0                                                                        |
| disk_mapped_cached_hitlists | 0                                                                        |
| killed_documents            | 0                                                                        |
| killed_rate                 | 0.00%                                                                    |
| ram_chunk                   | 86865484                                                                 |
| ram_chunk_segments_count    | 24                                                                       |
| disk_chunks                 | 1                                                                        |
| mem_limit                   | 134217728                                                                |
| mem_limit_rate              | 95.00%                                                                   |
| ram_bytes_retired           | 0                                                                        |
| optimizing                  | 1                                                                        |
| locked                      | 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":"-"} |
+-----------------------------+--------------------------------------------------------------------------+
29 rows in set (0.00 sec)