SHOW STATUS WHERE

When you use SHOW STATUS can can restrict with the LIKE syntax, allowing for a subset of values. For example:

mysql> SHOW GLOBAL STATUS LIKE 'Com%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Com_admin_commands       | 0     |
| Com_alter_db             | 0     |
| Com_alter_table          | 0     |
| Com_analyze              | 0     |
| Com_backup_table         | 0     |
| Com_begin                | 0     |
| Com_change_db            | 0     |
| Com_change_master        | 0     |
...

That’s great, but sometimes you want specific values. Using WHERE can achieve this. For Example.

mysql> SHOW GLOBAL STATUS WHERE VARIABLE_NAME IN (’Com_insert’,'Innodb_buffer_pool_pages_latched’,'threads_running’);
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Com_insert                       | 0     |
| Innodb_buffer_pool_pages_latched | 0     |
| Threads_running                  | 1     |
+----------------------------------+-------+
3 rows in set (0.00 sec)

Cool, the downside is you loose the wildcard capability, however you can string commands together with LIKE

mysql> SHOW GLOBAL STATUS WHERE VARIABLE_NAME LIKE 'innodb%' OR VARIABLE_NAME LIKE  'com%';

Comments

  1. Matthew Montgomery says

    In 5.1 you also have the option of INFORMATION_SCHEMA tables.

    mysql> select version();
    +—————–+
    | version() |
    +—————–+
    | 5.1.18-beta-log |
    +—————–+
    1 row in set (0.00 sec)

    mysql> select VARIABLE_NAME, floor(VARIABLE_VALUE) from information_schema.GLOBAL_STATUS where VARIABLE_NAME IN (‘Com_insert’,’Innodb_buffer_pool_pages_latched’, ‘Threads_running’) or VARIABLE_NAME like ‘Sort%';
    +———————————-+———————–+
    | VARIABLE_NAME | floor(VARIABLE_VALUE) |
    +———————————-+———————–+
    | COM_INSERT | 0 |
    | INNODB_BUFFER_POOL_PAGES_LATCHED | 0 |
    | SORT_MERGE_PASSES | 0 |
    | SORT_RANGE | 0 |
    | SORT_ROWS | 0 |
    | SORT_SCAN | 0 |
    | THREADS_RUNNING | 1 |
    +———————————-+———————–+
    7 rows in set (0.02 sec)