▪️ Node info and management
SHOW META [ LIKE pattern ]SHOW META is an SQL statement that shows additional meta-information about the latest query such as query time and keyword statistics. The syntax is:
- SQL
SELECT id,story_author FROM hn_small WHERE MATCH('one|two|three') limit 5;
SHOW META;+--------+--------------+
| id | story_author |
+--------+--------------+
| 300263 | throwaway37 |
| 713503 | mahmud |
| 716804 | mahmud |
| 776906 | jimbokun |
| 753332 | foxhop |
+--------+--------------+
5 rows in set (0.01 sec)
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| total | 5 |
| total_found | 266385 |
| total_relation | eq |
| time | 0.012 |
| keyword[0] | one |
| docs[0] | 224387 |
| hits[0] | 310327 |
| keyword[1] | three |
| docs[1] | 18181 |
| hits[1] | 21102 |
| keyword[2] | two |
| docs[2] | 63251 |
| hits[2] | 75961 |
+----------------+--------+
13 rows in set (0.00 sec)SHOW META can show IO and CPU counters, but they will only be available if searchd was started with --iostats and --cpustats switches respectively.
- SQL
SELECT id,channel_id FROM records WHERE MATCH('one|two|three') limit 5;
SHOW META;+--------+--------------+
| id | story_author |
+--------+--------------+
| 300263 | throwaway37 |
| 713503 | mahmud |
| 716804 | mahmud |
| 776906 | jimbokun |
| 753332 | foxhop |
+--------+--------------+
5 rows in set (0.01 sec)
+-----------------------+--------+
| Variable_name | Value |
+-----------------------+--------+
| total | 5 |
| total_found | 266385 |
| total_relation | eq |
| time | 0.011 |
| cpu_time | 18.004 |
| agents_cpu_time | 0.000 |
| io_read_time | 0.000 |
| io_read_ops | 0 |
| io_read_kbytes | 0.0 |
| io_write_time | 0.000 |
| io_write_ops | 0 |
| io_write_kbytes | 0.0 |
| agent_io_read_time | 0.000 |
| agent_io_read_ops | 0 |
| agent_io_read_kbytes | 0.0 |
| agent_io_write_time | 0.000 |
| agent_io_write_ops | 0 |
| agent_io_write_kbytes | 0.0 |
| keyword[0] | one |
| docs[0] | 224387 |
| hits[0] | 310327 |
| keyword[1] | three |
| docs[1] | 18181 |
| hits[1] | 21102 |
| keyword[2] | two |
| docs[2] | 63251 |
| hits[2] | 75961 |
+-----------------------+--------+
27 rows in set (0.00 sec)Additional predicted_time, dist_predicted_time, local_fetched_docs, local_fetched_hits, local_fetched_skips and their respective dist_fetched_* counterparts will only be available if searchd was configured with predicted time costs and query had predicted_time in the OPTION clause.
- SQL
SELECT id,story_author FROM hn_small WHERE MATCH('one|two|three') limit 5 option max_predicted_time=100;
SHOW META;+--------+--------------+
| id | story_author |
+--------+--------------+
| 300263 | throwaway37 |
| 713503 | mahmud |
| 716804 | mahmud |
| 776906 | jimbokun |
| 753332 | foxhop |
+--------+--------------+
5 rows in set (0.01 sec)
mysql> show meta;
+---------------------+--------+
| Variable_name | Value |
+---------------------+--------+
| total | 5 |
| total_found | 266385 |
| total_relation | eq |
| time | 0.012 |
| local_fetched_docs | 307212 |
| local_fetched_hits | 407390 |
| local_fetched_skips | 24 |
| predicted_time | 56 |
| keyword[0] | one |
| docs[0] | 224387 |
| hits[0] | 310327 |
| keyword[1] | three |
| docs[1] | 18181 |
| hits[1] | 21102 |
| keyword[2] | two |
| docs[2] | 63251 |
| hits[2] | 75961 |
+---------------------+--------+
17 rows in set (0.00 sec)SHOW META needs to run right after the query was executed in the same session. As some mysql connectors/libraries use connection pools, running SHOW META in a separate statement an lead to unexpected results like getting meta from another query. In these cases (and recommended in general) is to run a multiple statement containing query + SHOW META. Some connectors/libraries support o multi-queries on same method for single statement, other may require usage of a dedicated method for multi-queries or setting specific options at connection setup.
- SQL
SELECT id,story_author FROM hn_small WHERE MATCH('one|two|three') LIMIT 5; SHOW META;+--------+--------------+
| id | story_author |
+--------+--------------+
| 300263 | throwaway37 |
| 713503 | mahmud |
| 716804 | mahmud |
| 776906 | jimbokun |
| 753332 | foxhop |
+--------+--------------+
5 rows in set (0.01 sec)
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| total | 5 |
| total_found | 266385 |
| total_relation | eq |
| time | 0.011 |
| keyword[0] | one |
| docs[0] | 224387 |
| hits[0] | 310327 |
| keyword[1] | three |
| docs[1] | 18181 |
| hits[1] | 21102 |
| keyword[2] | two |
| docs[2] | 63251 |
| hits[2] | 75961 |
+----------------+--------+
13 rows in set (0.00 sec)You can also use the optional LIKE clause. It lets you pick just the variables that match a pattern. The pattern syntax is that of regular SQL wildcards, that is, % means any number of any characters, and _ means a single character.
- SQL
SHOW META LIKE 'total%';+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| total | 5 |
| total_found | 266385 |
| total_relation | eq |
+----------------+--------+
3 rows in set (0.00 sec)When using faceted search, you can check multiplier field in SHOW META output to see how many queries were run in an optimized group.
- SQL
SELECT * FROM facetdemo FACET brand_id FACET price FACET categories;
SHOW META LIKE 'multiplier';+------+-------+----------+---------------------+-------------+-------------+---------------------------------------+------------+
| id | price | brand_id | title | brand_name | property | j | categories |
+------+-------+----------+---------------------+-------------+-------------+---------------------------------------+------------+
| 1 | 306 | 1 | Product Ten Three | Brand One | Six_Ten | {"prop1":66,"prop2":91,"prop3":"One"} | 10,11 |
...
+----------+----------+
| brand_id | count(*) |
+----------+----------+
| 1 | 1013 |
...
+-------+----------+
| price | count(*) |
+-------+----------+
| 306 | 7 |
...
+------------+----------+
| categories | count(*) |
+------------+----------+
| 10 | 2436 |
...
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| multiplier | 4 |
+---------------+-------+
1 row in set (0.00 sec)When the cost-based query optimizer decides to use DocidIndex, ColumnarScan or SecondaryIndex in place of a plain filter, this is reflected in the SHOW META command.
The index variable lists the names and types of secondary indexes used while executing the query. The percent shows how many disk chunks (in the case of an RT index) or pseudo shards (in the case of a plain index) the secondary index was used on.
- SQL
SELECT count(*) FROM taxi1 WHERE tip_amount = 5;
SHOW META;+----------------+----------------------------------+
| Variable_name | Value |
+----------------+----------------------------------+
| total | 1 |
| total_found | 1 |
| total_relation | eq |
| time | 0.016 |
| index | tip_amount:SecondaryIndex (100%) |
+----------------+----------------------------------+
5 rows in set (0.00 sec)SHOW META can be used after executing a CALL PQ statement. In this case, it provides a different output.
SHOW META after a CALL PQ statement contains:
Total- total time spent on matching the document(s)Queries matched- how many stored queries match the document(s)Document matches- how many documents matched the queries stored in the tableTotal queries stored- number of queries stored in the tableTerm only queries- how many queries in the table have terms. The rest of the queries have extended query syntax.
- SQL
CALL PQ ('pq', ('{"title":"angry", "gid":3 }')); SHOW META;+------+
| id |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
+-----------------------+-----------+
| Name | Value |
+-----------------------+-----------+
| Total | 0.000 sec |
| Queries matched | 1 |
| Queries failed | 0 |
| Document matched | 1 |
| Total queries stored | 2 |
| Term only queries | 2 |
| Fast rejected queries | 1 |
+-----------------------+-----------+
7 rows in set (0.00 sec)CALL PQ with a verbose option gives a more detailed output.
It includes the following additional entries:
Setup- time spent on initial setup of the matching process: parsing docs, setting options, etc.Queries failed- number of queries that failedFast rejected queries- number of queries that were not fully evaluated, but quickly matched and rejected with filters or other conditionsTime per query- detailed times for each queryTime of matched queries- total time spent on queries that matched any documents
- SQL
CALL PQ ('pq', ('{"title":"angry", "gid":3 }'), 1 as verbose); SHOW META;+------+
| id |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
+-------------------------+-----------+
| Name | Value |
+-------------------------+-----------+
| Total | 0.000 sec |
| Setup | 0.000 sec |
| Queries matched | 1 |
| Queries failed | 0 |
| Document matched | 1 |
| Total queries stored | 2 |
| Term only queries | 2 |
| Fast rejected queries | 1 |
| Time per query | 69 |
| Time of matched queries | 69 |
+-------------------------+-----------+
10 rows in set (0.00 sec)