QUERY_CACHE_INFO Plugin

The QUERY_CACHE_INFO plugin allows you to see the contents of the query cache. It creates the QUERY_CACHE_INFO table in the INFORMATION_SCHEMA database that shows all queries that are in the cache. You must have the PROCESS privilege (see GRANT) to use this table. Querying of this table acquires the query cache lock and will result in lock waits for queries using/expiring the query cache.

The plugin is installed in the usual way (see Installing plugins), for example:

INSTALL PLUGIN query_cache_info SONAME 'query_cache_info';

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

New features will be implemented with MDEV-4682, including per-query statistics (must set HAVE_QUERY_CACHE_STATS at compile time).

The QUERY_CACHE_INFO table will be removed and replaced by the QUERY_CACHE_QUERIES table, and the new tables QUERY_CACHE_QUERIES_TABLES and QUERY_CACHE_TABLES will be added.

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_NAME`="some_table" AND `SCHEMA`="some_schema"
)

Selecting queries to cache / not cache

As a best practice, always check the use of the cache at development time and production time, and always check if the query cache isn't a problem instead of a cache solution.

Using the QUERY_CACHE_QUERIES table including statistics (set HAVE_QUERY_CACHE_STATS at compile time), we can better tune what queries could / could not be cached.

The main idea is to count how many cache hits we have, based upon the period between hits and select execution time.

QC_INFO plugin sum hits in LOW, HIGH and OUTLIER columns

A higher value of LOW shows that the query is well cached and hits with period between hits lower than execution time. In other words if we lost this query entry, we will need more time to execute the query again than if we had hit the query cache, so we win some I/O with this query cached

A higher value of HIGH shows that it's not a 'very bad' query, in other words the query has some hits that save I/O but if we lost this query entry, we will not see much lost time, since executing the query isn't time consuming, as the time between hits is lower than the query execution time.

A higher value of OUTLIER show that the query doesn't have a good hit rate, but we should consider the time to execute this query. It may be ok to cache a query that expends more time to execute.