The Ultimate Guide to High Availability with MariaDB

Information Schema QUERY_CACHE_INFO Table

You are viewing an old version of this article. View the current version here.

Description

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

The QUERY_CACHE_INFO table is created by the QUERY_CACHE_INFO plugin, and allows you 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. You must have the PROCESS privilege (see GRANT) to use this table.

It contains the following columns:

ColumnDescription
STATEMENT_SCHEMADatabase used when query was included
STATEMENT_TEXTQuery text
RESULT_BLOCKS_COUNTNumber of result blocks
RESULT_BLOCKS_SIZESize in bytes of result blocks
RESULT_BLOCKS_SIZE_USEDSize in bytes of used blocks
LIMITAdded MariaDB 10.1.8.
MAX_SORT_LENGTHAdded MariaDB 10.1.8.
GROUP_CONCAT_MAX_LENGTHAdded MariaDB 10.1.8.
CHARACTER_SET_CLIENTAdded MariaDB 10.1.8.
CHARACTER_SET_RESULTAdded MariaDB 10.1.8.
COLLATION Added MariaDB 10.1.8.
TIMEZONEAdded MariaDB 10.1.8.
DEFAULT_WEEK_FORMATAdded MariaDB 10.1.8.
DIV_PRECISION_INCREMENTAdded MariaDB 10.1.8.
SQL_MODEAdded MariaDB 10.1.8.
LC_TIME_NAMESAdded MariaDB 10.1.8.
CLIENT_LONG_FLAGAdded MariaDB 10.1.8.
CLIENT_PROTOCOL_41Added MariaDB 10.1.8.
PROTOCOL_TYPEAdded MariaDB 10.1.8.
MORE_RESULTS_EXISTSAdded MariaDB 10.1.8.
IN_TRANSAdded MariaDB 10.1.8.
AUTOCOMMITAdded MariaDB 10.1.8.
PACKET_NUMBERAdded MariaDB 10.1.8.
HITSIncremented each time the query cache is hit. Added MariaDB 10.3.2.

For example:

SELECT * FROM information_schema.QUERY_CACHE_INFO;
+------------------+-----------------+---------------------+--------------------+-------------------------+
| STATEMENT_SCHEMA | STATEMENT_TEXT  | RESULT_BLOCKS_COUNT | RESULT_BLOCKS_SIZE | RESULT_BLOCKS_SIZE_USED |
+------------------+-----------------+---------------------+--------------------+-------------------------+
...
| test             | SELECT * FROM a |                   1 |                512 |                     143 |
| test             | select * FROM a |                   1 |                512 |                     143 |
...
+------------------+-----------------+---------------------+--------------------+-------------------------

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.