Usually unused indexes are devil, they waste diskspace, cache, they make INSERT / DELETE / UPDATE operations slower and what makes them worse – it is hard to find them.
But now ( with userstatsV2.patch) you can find all unused indexes (since last restart of mysqld) by single query
|
1 2 3 4 5 6 |
SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME FROM information_schema.statistics `s` LEFT JOIN information_schema.index_statistics INDXS ON (s.TABLE_SCHEMA = INDXS.TABLE_SCHEMA AND s.TABLE_NAME=INDXS.TABLE_NAME AND s.INDEX_NAME=INDXS.INDEX_NAME) WHERE INDXS.TABLE_SCHEMA IS NULL; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
+--------------+---------------------------+-----------------+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | +--------------+---------------------------+-----------------+ | art100 | article100 | ext_key | | art100 | article100 | site_id | | art100 | article100 | hash | | art100 | article100 | forum_id_2 | | art100 | article100 | published | | art100 | article100 | inserted | | art100 | article100 | site_id_2 | | art100 | author100 | PRIMARY | | art100 | author100 | site_id | ... +--------------+---------------------------+-----------------+ 1150 rows in set (1 min 44.23 sec) |
As you see query is not fast, mainly because information_schema.statistics is slow by itself, but in any case very helpful.
For some versions of the patch, where the table has only the INDEX_NAME and the ROWS_READ columns, you can use this query:
|
1 2 3 4 5 |
SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME FROM information_schema.statistics `s` LEFT JOIN information_schema.index_statistics IST ON CONCAT_WS('.', s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME) = IST.INDEX_NAME WHERE IST.INDEX_NAME IS NULL; |
