Node info and management > SHOW QUERIES

SHOW META [ LIKE pattern ]

SHOW META is an SQL statement that displays additional meta-information about the processed query, including the query time, keyword statistics, and information about the secondary indexes used. The syntax is:

‹›

SQL

📋

SELECT id, story_author FROM hn_small WHERE MATCH('one|two|three') and comment_ranking > 2 limit 5;
show meta;

‹›

Response

+---------+--------------+
| id      | story_author |
+---------+--------------+
|  151171 | anewkid      |
|  302758 | bks          |
|  805806 | drRoflol     |
| 1099245 | tnorthcutt   |
|  303252 | whiten       |
+---------+--------------+
5 rows in set (0.00 sec)

+----------------+---------------------------------------+
| Variable_name  | Value                                 |
+----------------+---------------------------------------+
| total          | 5                                     |
| total_found    | 2308                                  |
| total_relation | eq                                    |
| time           | 0.001                                 |
| 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                                 |
| index          | comment_ranking:SecondaryIndex (100%) |
+----------------+---------------------------------------+
14 rows in set (0.00 sec)

SHOW META can display I/O and CPU counters, but they will only be available if searchd was started with the --iostats and --cpustats switches, respectively.

‹›

SQL

📋

SELECT id,channel_id FROM records WHERE MATCH('one|two|three') limit 5;

SHOW META;

‹›

Response

+--------+--------------+
| 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 values, such as 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 the query included 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;

‹›

Response

+--------+--------------+
| 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 must be executed immediately after the query in the same session. Since some MySQL connectors/libraries use connection pools, running SHOW META in a separate statement can lead to unexpected results, such as retrieving metadata from another query. In these cases (and generally recommended), run a multiple statement containing both the query and SHOW META. Some connectors/libraries support multi-queries within the same method for a single statement, while others may require the use of a dedicated method for multi-queries or setting specific options during connection setup.

‹›

SQL

📋

SELECT id,story_author FROM hn_small WHERE MATCH('one|two|three') LIMIT 5; SHOW META;

‹›

Response

+--------+--------------+
| 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, which allows you to select only the variables that match a specific pattern. The pattern syntax follows standard SQL wildcards, where % represents any number of any characters, and _ represents a single character.

‹›

SQL

📋

SHOW META LIKE 'total%';

‹›

Response

+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| total          | 5      |
| total_found    | 266385 |
| total_relation | eq     |
+----------------+--------+
3 rows in set (0.00 sec)

When utilizing faceted search, you can examine the multiplier field in the SHOW META output to determine how many queries were executed in an optimized group.

‹›

SQL

📋

SELECT * FROM facetdemo FACET brand_id FACET price FACET categories;
SHOW META LIKE 'multiplier';

‹›

Response

+------+-------+----------+---------------------+-------------+-------------+---------------------------------------+------------+
| 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 chooses to use DocidIndex, ColumnarScan, or SecondaryIndex instead of a plain filter, this is reflected in the SHOW META command.

The index variable displays the names and types of secondary indexes used during query execution. The percentage indicates how many disk chunks (in the case of an RT index) or pseudo shards (in the case of a plain index) utilized the secondary index.

‹›

SQL

📋

SELECT count(*) FROM taxi1 WHERE tip_amount = 5;
SHOW META;

‹›

Response

+----------------+----------------------------------+
| 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 which case it provides different output.

SHOW META following a CALL PQ statement includes:

Total - Total time spent on matching the document(s)
Queries matched - Number of stored queries that match the document(s)
Document matches - Number of documents that matched the queries stored in the table
Total queries stored - Total number of queries stored in the table
Term only queries - Number of queries in the table that have terms; the remaining queries use extended query syntax.

‹›

SQL

📋

CALL PQ ('pq', ('{"title":"angry", "gid":3 }')); SHOW META;

‹›

Response

+------+
| 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)

Using CALL PQ with a verbose option provides more detailed output.

It includes the following additional entries:

Setup - Time spent on the initial setup of the matching process, such as parsing docs and setting options
Queries failed - Number of queries that failed
Fast rejected queries - Number of queries that were not fully evaluated but quickly matched and rejected using filters or other conditions
Time per query - Detailed time for each query
Time 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;

‹›

Response

+------+
| 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)

SHOW THREADS

SHOW THREADS [ OPTION columns=width[,format=sphinxql][,format=all] ]

SHOW THREADS is an SQL statement that displays information about all threads and their current activities.

The resulting table contains the following columns:

Tid: ID assigned to the thread by the kernel
Name: Thread name, also visible in top, htop, ps, and other Unix tools for monitoring thread statistics
Proto: Connection protocol; possible values include sphinx, mysql, http, ssl, compressed, replication, or a combination (e.g., http,ssl or compressed,mysql)
State: Thread state; possible values are handshake, net_read, net_write, query, net_idle
Host: Client's ip:port
ConnID: Connection ID (starting from 0)
Time: Current job's duration (in seconds, with microsecond precision) or thread uptime when using format=all and the thread is idling
Work time: Thread uptime
Work time CPU: Effective CPU time (requires --cpustats)
Jobs done: Number of jobs completed by this thread
Last job took: Duration of the last job
In idle: Whether the thread is currently idling or when it last idled
Info: Information about the query, which may include multiple queries if the query targets a distributed table or a real-time table

‹›

SQL
JSON
PHP
Python
javascript
Java
C#

📋

SHOW THREADS;

POST /cli -d "SHOW THREADS"

$client->nodes()->threads();

utilsApi.sql('SHOW THREADS')

res = await utilsApi.sql('SHOW THREADS');

utilsApi.sql("SHOW THREADS");

utilsApi.Sql("SHOW THREADS");

‹›

Response

*************************** 1. row ***************************
           Tid: 31797
          Name: work_3
         Proto: mysql
         State: query
          Host: 127.0.0.1:43388
        ConnID: 4931
          Time: 0.000903
     Work time: 2s
 Work time CPU: 0us
Thd efficiency: 0.00%
     Jobs done: 2066
 Last job took: 930us
       In idle: No (working)
          Info: insert into t values(0,'abc'),(0,'def')
*************************** 2. row ***************************
           Tid: 31799
          Name: work_5
         Proto: mysql
         State: query
          Host: 127.0.0.1:43390
        ConnID: 4932
          Time: 0.000042
     Work time: 2s
 Work time CPU: 0us
Thd efficiency: 0.00%
     Jobs done: 2072
 Last job took: 66us
       In idle: No (working)
          Info: show threads

{
  "columns": [
    {
      "Tid": {
        "type": "string"
      }
    },
    {
      "Name": {
        "type": "string"
      }
    },
    {
      "Proto": {
        "type": "string"
      }
    },
    {
      "State": {
        "type": "string"
      }
    },
    {
      "Host": {
        "type": "string"
      }
    },
    {
      "ConnID": {
        "type": "string"
      }
    },
    {
      "Time": {
        "type": "string"
      }
    },
    {
      "Work time": {
        "type": "string"
      }
    },
    {
      "Work time CPU": {
        "type": "string"
      }
    },
    {
      "Thd efficiency": {
        "type": "string"
      }
    },
    {
      "Jobs done": {
        "type": "string"
      }
    },
    {
      "Last job took": {
        "type": "string"
      }
    },
    {
      "In idle": {
        "type": "string"
      }
    },
    {
      "Info": {
        "type": "string"
      }
    }
  ],
  "data": [
    {
      "Tid": 6844,
      "Name": "work_3",
      "Proto": "http",
      "State": "query",
      "Host": "127.0.0.1:51752",
      "ConnID": 91,
      "Time": 3245,
      "Work time": "2h",
      "Work time CPU": "0us",
      "Thd efficiency": "0.00%",
      "Jobs done": 1073587960,
      "Last job took": "16ms",
      "In idle": "No (working)",
      "Info": "show_threads"
    }
  ],
  "total": 0,
  "error": "",
  "warning": ""
}

Array
(
    [31796] => Array
        (
            [Name] => work_2
            [Proto] => mysql
            [State] => query
            [Host] => 127.0.0.1:39320
            [ConnID] => 2897
            [Time] => 517
            [Work time] => 1s
            [Work time CPU] => 0us
            [Thd efficiency] => 0.00%
            [Jobs done] => 1228
            [Last job took] => 526us
            [In idle] => No (working)
            [Info] => insert into t values(0,'abc'),(0,'def')
        )

    [31798] => Array
        (
            [Name] => work_4
            [Proto] => http
            [State] => query
            [Host] => 127.0.0.1:37748
            [ConnID] => 2898
            [Time] => 38
            [Work time] => 883ms
            [Work time CPU] => 0us
            [Thd efficiency] => 0.00%
            [Jobs done] => 1237
            [Last job took] => 76us
            [In idle] => No (working)
            [Info] => show_threads
        )

)

{
  u'columns': [
    {
      u'Tid': {
        u'type': u'string'
      }
    },
    {
      u'Name': {
        u'type': u'string'
      }
    },
    {
      u'Proto': {
        u'type': u'string'
      }
    },
    {
      u'State': {
        u'type': u'string'
      }
    },
    {
      u'Host': {
        u'type': u'string'
      }
    },
    {
      u'ConnID': {
        u'type': u'string'
      }
    },
    {
      u'Time': {
        u'type': u'string'
      }
    },
    {
      u'Work time': {
        u'type': u'string'
      }
    },
    {
      u'Work time CPU': {
        u'type': u'string'
      }
    },
    {
      u'Thd efficiency': {
        u'type': u'string'
      }
    },
    {
      u'Jobs done': {
        u'type': u'string'
      }
    },
    {
      u'Last job took': {
        u'type': u'string'
      }
    },
    {
      u'In idle': {
        u'type': u'string'
      }
    },
    {
      u'Info': {
        u'type': u'string'
      }
    }
  ],
  u'data': [
    {
      u'Tid': 6844,
      u'Name': u'work_3',
      u'Proto': u'http',
      u'State': u'query',
      u'Host': u'127.0.0.1:51752',
      u'ConnID': 91,
      u'Time': 3245,
      u'Work time': u'2h',
      u'Work time CPU': u'0us',
      u'Thd efficiency': u'0.00%',
      u'Jobs done': 1073587960,
      u'Last job took': u'16ms',
      u'In idle': u'No (working)',
      u'Info': u'show_threads'
    }
  ],
  u'total': 0,
  u'error': u'',
  u'warning': u''
}

{
  "columns": [
    {
      "Tid": {
        "type": "string"
      }
    },
    {
      "Name": {
        "type": "string"
      }
    },
    {
      "Proto": {
        "type": "string"
      }
    },
    {
      "State": {
        "type": "string"
      }
    },
    {
      "Host": {
        "type": "string"
      }
    },
    {
      "ConnID": {
        "type": "string"
      }
    },
    {
      "Time": {
        "type": "string"
      }
    },
    {
      "Work time": {
        "type": "string"
      }
    },
    {
      "Work time CPU": {
        "type": "string"
      }
    },
    {
      "Thd efficiency": {
        "type": "string"
      }
    },
    {
      "Jobs done": {
        "type": "string"
      }
    },
    {
      "Last job took": {
        "type": "string"
      }
    },
    {
      "In idle": {
        "type": "string"
      }
    },
    {
      "Info": {
        "type": "string"
      }
    }
  ],
  "data": [
    {
      "Tid": 6844,
      "Name": "work_3",
      "Proto": "http",
      "State": "query",
      "Host": "127.0.0.1:51752",
      "ConnID": 91,
      "Time": 3245,
      "Work time": "2h",
      "Work time CPU": "0us",
      "Thd efficiency": "0.00%",
      "Jobs done": 1073587960,
      "Last job took": "16ms",
      "In idle": "No (working)",
      "Info": "show_threads"
    }
  ],
  "total": 0,
  "error": "",
  "warning": ""
}


{
  columns=[
    {
      Tid={
        type=string
      }
    },
    {
      Name={
        type=string
      }
    },
    {
      Proto={
        type=string
      }
    },
    {
      State={
        type=string
      }
    },
    {
      Host={
        type=string
      }
    },
    {
      ConnID={
        type=string
      }
    },
    {
      Time={
        type=string
      }
    },
    {
      Work time={
        type=string
      }
    },
    {
      Work time CPU={
        type=string
      }
    },
    {
      Thd efficiency={
        type=string
      }
    },
    {
      Jobs done={
        type=string
      }
    },
    {
      Last job took={
        type=string
      }
    },
    {
      In idle={
        type=string
      }
    },
    {
      Info={
        type=string
      }
    }
  ],
  data=[
    {
      Tid=6844,
      Name=work_3,
      Proto=http,
      State=query,
      Host=127.0.0.1:51752,
      ConnID=91,
      Time=3245,
      Work time=2h,
      Work time CPU=0us,
      Thd efficiency=0.00%,
      Jobs done=1073587960,
      Last job took=16ms,
      In idle=No (working),
      Info=show_threads
    }
  ],
  total=0,
  error=,
  warning=
}


{
  columns=[
    {
      Tid={
        type=string
      }
    },
    {
      Name={
        type=string
      }
    },
    {
      Proto={
        type=string
      }
    },
    {
      State={
        type=string
      }
    },
    {
      Host={
        type=string
      }
    },
    {
      ConnID={
        type=string
      }
    },
    {
      Time={
        type=string
      }
    },
    {
      Work time={
        type=string
      }
    },
    {
      Work time CPU={
        type=string
      }
    },
    {
      Thd efficiency={
        type=string
      }
    },
    {
      Jobs done={
        type=string
      }
    },
    {
      Last job took={
        type=string
      }
    },
    {
      In idle={
        type=string
      }
    },
    {
      Info={
        type=string
      }
    }
  ],
  data=[
    {
      Tid=6844,
      Name=work_3,
      Proto=http,
      State=query,
      Host=127.0.0.1:51752,
      ConnID=91,
      Time=3245,
      Work time=2h,
      Work time CPU=0us,
      Thd efficiency=0.00%,
      Jobs done=1073587960,
      Last job took=16ms,
      In idle=No (working),
      Info=show_threads
    }
  ],
  total=0,
  error="",
  warning=""
}

The Info column displays:

Raw text of queries executed via the Manticore SQL interface
Full text syntax, comments, and data size for queries run through the internal Manticore binary protocol (e.g., from a remote Manticore instance)

You can limit the maximum width of the Info column by specifying the columns=N option (note the second row in the example table).

By default, queries are displayed in their original format. However, when the format=sphinxql option is used, queries will be shown in SQL format, regardless of the protocol used for execution.

Using format=all will show all threads, while idling and system threads are hidden without this option (e.g., those busy with OPTIMIZE).

‹›

SQL
JSON
PHP
Python
javascript
Java
C#

📋

SHOW THREADS OPTION columns=30\G

POST /cli -d "SHOW THREADS OPTION columns=30"

$client->nodes()->threads(['body'=>['columns'=>30]]);

utilsApi.sql('SHOW THREADS OPTION columns=30')

res = await utilsApi.sql('SHOW THREADS OPTION columns=30');

utilsApi.sql("SHOW THREADS OPTION columns=30");

utilsApi.Sql("SHOW THREADS OPTION columns=30");

‹›

Response

mysql> show threads option columns=30\G
*************************** 1. row ***************************
           Tid: 9156
          Name: work_2
         Proto: mysql
         State: query
          Host: 127.0.0.1:53298
        ConnID: 20112
          Time: 0.002291
     Work time: 12s
 Work time CPU: 0us
Thd efficiency: 0.00%
     Jobs done: 8497
 Last job took: 2ms
       In idle: No (working)
          Info: insert into t values(0,'abc'),
*************************** 2. row ***************************
           Tid: 9159
          Name: work_5
         Proto: mysql
         State: query
          Host: 127.0.0.1:57698
        ConnID: 8196
          Time: 0.000042
     Work time: 11s
 Work time CPU: 0us
Thd efficiency: 0.00%
     Jobs done: 8547
 Last job took: 78us
       In idle: No (working)
          Info: show threads option columns=30
2 rows in set (0.00 sec)

{
  "columns": [
    {
      "Tid": {
        "type": "string"
      }
    },
    {
      "Name": {
        "type": "string"
      }
    },
    {
      "Proto": {
        "type": "string"
      }
    },
    {
      "State": {
        "type": "string"
      }
    },
    {
      "Host": {
        "type": "string"
      }
    },
    {
      "ConnID": {
        "type": "string"
      }
    },
    {
      "Time": {
        "type": "string"
      }
    },
    {
      "Work time": {
        "type": "string"
      }
    },
    {
      "Work time CPU": {
        "type": "string"
      }
    },
    {
      "Thd efficiency": {
        "type": "string"
      }
    },
    {
      "Jobs done": {
        "type": "string"
      }
    },
    {
      "Last job took": {
        "type": "string"
      }
    },
    {
      "In idle": {
        "type": "string"
      }
    },
    {
      "Info": {
        "type": "string"
      }
    }
  ],
  "data": [
    {
      "Tid": 6844,
      "Name": "work_3",
      "Proto": "http",
      "State": "query",
      "Host": "127.0.0.1:51752",
      "ConnID": 91,
      "Time": 3245,
      "Work time": "2h",
      "Work time CPU": "0us",
      "Thd efficiency": "0.00%",
      "Jobs done": 1073587960,
      "Last job took": "16ms",
      "In idle": "No (working)",
      "Info": "show_threads"
    }
  ],
  "total": 0,
  "error": "",
  "warning": ""
}

Array
(
    [31795] => Array
        (
            [Name] => work_1
            [Proto] => mysql
            [State] => query
            [Host] => 127.0.0.1:57676
            [ConnID] => 12075
            [Time] => 326
            [Work time] => 3s
            [Work time CPU] => 0us
            [Thd efficiency] => 0.00%
            [Jobs done] => 5092
            [Last job took] => 335us
            [In idle] => No (working)
            [Info] => insert into t values(0,'abc'),
        )

    [31797] => Array
        (
            [Name] => work_3
            [Proto] => http
            [State] => query
            [Host] => 127.0.0.1:56104
            [ConnID] => 12076
            [Time] => 33
            [Work time] => 4s
            [Work time CPU] => 0us
            [Thd efficiency] => 0.00%
            [Jobs done] => 5045
            [Last job took] => 82us
            [In idle] => No (working)
            [Info] => show_threads
        )

)

{
  u'columns': [
    {
      u'Tid': {
        u'type': u'string'
      }
    },
    {
      u'Name': {
        u'type': u'string'
      }
    },
    {
      u'Proto': {
        u'type': u'string'
      }
    },
    {
      u'State': {
        u'type': u'string'
      }
    },
    {
      u'Host': {
        u'type': u'string'
      }
    },
    {
      u'ConnID': {
        u'type': u'string'
      }
    },
    {
      u'Time': {
        u'type': u'string'
      }
    },
    {
      u'Work time': {
        u'type': u'string'
      }
    },
    {
      u'Work time CPU': {
        u'type': u'string'
      }
    },
    {
      u'Thd efficiency': {
        u'type': u'string'
      }
    },
    {
      u'Jobs done': {
        u'type': u'string'
      }
    },
    {
      u'Last job took': {
        u'type': u'string'
      }
    },
    {
      u'In idle': {
        u'type': u'string'
      }
    },
    {
      u'Info': {
        u'type': u'string'
      }
    }
  ],
  u'data': [
    {
      u'Tid': 6844,
      u'Name': u'work_3',
      u'Proto': u'http',
      u'State': u'query',
      u'Host': u'127.0.0.1:51752',
      u'ConnID': 91,
      u'Time': 3245,
      u'Work time': u'2h',
      u'Work time CPU': u'0us',
      u'Thd efficiency': u'0.00%',
      u'Jobs done': 1073587960,
      u'Last job took': u'16ms',
      u'In idle': u'No (working)',
      u'Info': u'show_threads'
    }
  ],
  u'total': 0,
  u'error': u'',
  u'warning': u''
}

{
  "columns": [
    {
      "Tid": {
        "type": "string"
      }
    },
    {
      "Name": {
        "type": "string"
      }
    },
    {
      "Proto": {
        "type": "string"
      }
    },
    {
      "State": {
        "type": "string"
      }
    },
    {
      "Host": {
        "type": "string"
      }
    },
    {
      "ConnID": {
        "type": "string"
      }
    },
    {
      "Time": {
        "type": "string"
      }
    },
    {
      "Work time": {
        "type": "string"
      }
    },
    {
      "Work time CPU": {
        "type": "string"
      }
    },
    {
      "Thd efficiency": {
        "type": "string"
      }
    },
    {
      "Jobs done": {
        "type": "string"
      }
    },
    {
      "Last job took": {
        "type": "string"
      }
    },
    {
      "In idle": {
        "type": "string"
      }
    },
    {
      "Info": {
        "type": "string"
      }
    }
  ],
  "data": [
    {
      "Tid": 6844,
      "Name": "work_3",
      "Proto": "http",
      "State": "query",
      "Host": "127.0.0.1:51752",
      "ConnID": 91,
      "Time": 3245,
      "Work time": "2h",
      "Work time CPU": "0us",
      "Thd efficiency": "0.00%",
      "Jobs done": 1073587960,
      "Last job took": "16ms",
      "In idle": "No (working)",
      "Info": "show_threads"
    }
  ],
  "total": 0,
  "error": "",
  "warning": ""
}


{
  columns=[
    {
      Tid={
        type=string
      }
    },
    {
      Name={
        type=string
      }
    },
    {
      Proto={
        type=string
      }
    },
    {
      State={
        type=string
      }
    },
    {
      Host={
        type=string
      }
    },
    {
      ConnID={
        type=string
      }
    },
    {
      Time={
        type=string
      }
    },
    {
      Work time={
        type=string
      }
    },
    {
      Work time CPU={
        type=string
      }
    },
    {
      Thd efficiency={
        type=string
      }
    },
    {
      Jobs done={
        type=string
      }
    },
    {
      Last job took={
        type=string
      }
    },
    {
      In idle={
        type=string
      }
    },
    {
      Info={
        type=string
      }
    }
  ],
  data=[
    {
      Tid=6844,
      Name=work_3,
      Proto=http,
      State=query,
      Host=127.0.0.1:51752,
      ConnID=91,
      Time=3245,
      Work time=2h,
      Work time CPU=0us,
      Thd efficiency=0.00%,
      Jobs done=1073587960,
      Last job took=16ms,
      In idle=No (working),
      Info=show_threads
    }
  ],
  total=0,
  error=,
  warning=
}


{
  columns=[
    {
      Tid={
        type=string
      }
    },
    {
      Name={
        type=string
      }
    },
    {
      Proto={
        type=string
      }
    },
    {
      State={
        type=string
      }
    },
    {
      Host={
        type=string
      }
    },
    {
      ConnID={
        type=string
      }
    },
    {
      Time={
        type=string
      }
    },
    {
      Work time={
        type=string
      }
    },
    {
      Work time CPU={
        type=string
      }
    },
    {
      Thd efficiency={
        type=string
      }
    },
    {
      Jobs done={
        type=string
      }
    },
    {
      Last job took={
        type=string
      }
    },
    {
      In idle={
        type=string
      }
    },
    {
      Info={
        type=string
      }
    }
  ],
  data=[
    {
      Tid=6844,
      Name=work_3,
      Proto=http,
      State=query,
      Host=127.0.0.1:51752,
      ConnID=91,
      Time=3245,
      Work time=2h,
      Work time CPU=0us,
      Thd efficiency=0.00%,
      Jobs done=1073587960,
      Last job took=16ms,
      In idle=No (working),
      Info=show_threads
    }
  ],
  total=0,
  error="",
  warning=""
}

SHOW META SHOW QUERIES

SHOW QUERIES

SHOW QUERIES returns information about all currently running queries. The output is a table with the following structure:

id: Query ID that can be used in KILL to terminate the query
query: Query statement or a portion of it
protocol: Connection protocol, with possible values being sphinx, mysql, http, ssl, compressed, replication, or a combination (e.g., http,ssl or compressed,mysql)
host: Client's ip:port

‹›

SQL

📋

mysql> SHOW QUERIES;

‹›

Response

+------+--------------+----------+-----------------+
| id   | query        | protocol | host            |
+--------------------------+-----------------------+
|    6 | select       | http     | 127.0.0.1:41128 |
|    4 | show queries | mysql    | 127.0.0.1:56672 |
+------+--------------+----------+-----------------+
2 rows in set (0.61 sec)

Refer to SHOW THREADS if you'd like to gain insight from the perspective of the threads themselves.

SHOW THREADS KILL

KILL <query id>

KILL terminates the execution of a query by its ID, which you can find in SHOW QUERIES.

‹›

SQL

📋

mysql> KILL 4;
Query OK, 1 row affected (0.00 sec)

SHOW QUERIES SHOW WARNINGS

SHOW META

SHOW META and facets

SHOW META and query optimizer

SHOW META for PQ tables

SHOW THREADS

SHOW QUERIES

KILL