Information Schema INDEX_STATISTICS Table

The Information Schema INDEX_STATISTICS table provides statistics on index usage, helping to identify unused indexes and optimize performance.

The Information Schema INDEX_STATISTICS table shows statistics on index usage and makes it possible to do such things as locating unused indexes and generating the commands to remove them.

This is part of the User Statistics feature, which is not enabled by default.

It contains the following columns:

Field	Type	Notes
TABLE_SCHEMA	VARCHAR(192)	The schema (database) name.
TABLE_NAME	VARCHAR(192)	The table name.
INDEX_NAME	VARCHAR(192)	The index name (as visible in SHOW CREATE TABLE).
ROWS_READ	BIGINT(21)	The number of rows read from this index.
QUERIES	BIGINT(21)	Incremented for each index the query is part of. This assists one to see how effective the index is. From MariaDB 11.5

Example

SELECT * FROM information_schema.INDEX_STATISTICS 
WHERE TABLE_NAME = "author";
+--------------+------------+------------+-----------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
+--------------+------------+------------+-----------+
| books        | author     | by_name    |        15 |
+--------------+------------+------------+-----------+

_{This page is licensed: CC BY-SA / Gnu FDL}