Information Schema QUERY_CACHE_INFO Table

Description

The table is not a standard Information Schema table, and is a MariaDB extension.

The QUERY_CACHE_INFO plugin allows to see the contents of the query cache. It creates a table in the INFORMATION_SCHEMA database that shows all queries that are in the cache. One must have the PROCESS privilege (see GRANT) to use this table.

SELECT statement_schema, statement_text, result_blocks_count, result_blocks_size FROM information_schema.query_cache_info;
+------------------+------------------+---------------------+--------------------+
| statement_schema | statement_text   | result_blocks_count | result_blocks_size |
+------------------+------------------+---------------------+--------------------+
| test             | select * from t1 |                   1 |                512 |
+------------------+------------------+---------------------+--------------------+

Statistics and new features (MDEV-4682)

New features are planned (at JIRA) to be implemented, including a per query statistics, for more info check MDEV-4682 (not revised yet and not included at MariaDB code).

MDEV-4682 will remove QUERY_CACHE_INFO table, this will be replaced by QUERY_CACHE_QUERIES table with more fields. Fields are:

• QUERY_CACHE_ID - cache entry id (only for qc_info plugin, internally server use a hash table)
• STATEMENT_SCHEMA - schema used when query was included
• STATEMENT_TEXT - query text
• RESULT_FOUND_ROWS
• QUERY_ROWS
• SELECT_ROWS_READ
• QUERY_HITS - Query cache hits in this entry
• QUERY_HITS_PERIOD_LOW - period between hits that is faster than executing the query without query cache (a good hit rate have many hits here), removing this query entry will consume more time than returning value from query cache
• QUERY_HITS_PERIOD_HIGH - period between hits that is slower than executing the query without query cache and is 10 times faster than executing the query without query cache, but isn't slow than 1 second, removing this query will not give problems to users but will increase disk i/o
• QUERY_HITS_PERIOD_OUTLIERS - period between hits that is higher than is not HIGH or LOW
• QUERY_HITS_TOTAL_TIME_US - total time expend with query cache
• QUERY_HITS_MEAN_PERIOD_US - mean query cache period between hits
• QUERY_HITS_MEAN_PERIOD_LOW_US - mean query cache period between LOW hits
• QUERY_HITS_MEAN_PERIOD_HIGH_US - mean query cache period between HIGH hits
• QUERY_INSERT_TIME - time that query was inserted at query cache
• QUERY_LAST_HIT_TIME - last time the query was hit at query cache
• SELECT_EXPEND_TIME_US - query expend time
• SELECT_LOCK_TIME_US - query lock expend time
• TABLES_TYPE - type of table (transactional / non transactional, and others)
• RESULT_LENGTH - length of result
• RESULT_BLOCKS_COUNT - number of result blocks
• RESULT_BLOCKS_SIZE - size of result blocks
• RESULT_BLOCKS_SIZE_USED - size of used blocks
• FLAGS_CLIENT_LONG_FLAG - connection flag (part of query cache hash)
• FLAGS_CLIENT_PROTOCOL_41 - connection flag (part of query cache hash)
• FLAGS_PROTOCOL_TYPE - connection flag (part of query cache hash)
• FLAGS_MORE_RESULTS_EXISTS - connection flag (part of query cache hash)
• FLAGS_IN_TRANS - connection flag (part of query cache hash)
• FLAGS_AUTOCOMMIT - connection flag (part of query cache hash)
• FLAGS_PKT_NR - connection flag (part of query cache hash)
• FLAGS_CHARACTER_SET_CLIENT - connection flag (part of query cache hash)
• FLAGS_CHARACTER_SET_RESULTS - connection flag (part of query cache hash)
• FLAGS_COLLATION_CONNECTION - connection flag (part of query cache hash)
• FLAGS_LIMIT - connection flag (part of query cache hash)
• FLAGS_TIME_ZONE - connection flag (part of query cache hash)
• FLAGS_SQL_MODE - connection flag (part of query cache hash)
• FLAGS_MAX_SORT_LENGTH - connection flag (part of query cache hash)
• FLAGS_GROUP_CONCAT_MAX_LEN - connection flag (part of query cache hash)
• FLAGS_DIV_PRECISION_INCREMENT - connection flag (part of query cache hash)
• FLAGS_DEFAULT_WEEK_FORMAT - connection flag (part of query cache hash)
• FLAGS_LC_TIME_NAMES - connection flag (part of query cache hash) <</code>>

QUERY_CACHE_QUERIES_TABLES table return all tables used in a query cache entry

*QUERY_CACHE_ID - cache entry id (only for qc_info plugin, internally server use a hash table), use with QUERY_CACHE_QUERIES table
*SCHEMA - table database schema 
*TABLE - table name

The query cache entry is identified by QUERY_CACHE_ID column at QUERY_CACHE_QUERIES table, you could get all queries from a table using:

SELECT *
FROM information_schema.QUERY_CACHE_QUERIES
WHERE QUERY_CACHE_ID IN (
  SELECT QUERY_CACHE_ID 
  FROM information_schema.QUERY_CACHE_QUERIES_TABLES 
  WHERE `TABLE`="some_table" AND `SCHEMA`="some_schema"
)

QUERY_CACHE_TABLES table is included and show all tables inside query cache and how many queries it have cached, columns of this table:

TABLE_SCHEMA - table database schema
TABLE_NAME - table name
TABLE_HASHED - internal use
TABLE_TYPE - transactional / non transactional - internal use
QUERIES_IN_CACHE - number of queries in query cache using this table