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 |
<br>SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME<br>FROM information_schema.statistics `s` LEFT JOIN information_schema.index_statistics INDXS<br>ON (s.TABLE_SCHEMA = INDXS.TABLE_SCHEMA AND<br>s.TABLE_NAME=INDXS.TABLE_NAME AND<br>s.INDEX_NAME=INDXS.INDEX_NAME)<br>WHERE INDXS.TABLE_SCHEMA IS NULL;<br> |
|
1 |
<br>+--------------+---------------------------+-----------------+<br>| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME |<br>+--------------+---------------------------+-----------------+<br>| art100 | article100 | ext_key | <br>| art100 | article100 | site_id | <br>| art100 | article100 | hash | <br>| art100 | article100 | forum_id_2 | <br>| art100 | article100 | published | <br>| art100 | article100 | inserted | <br>| art100 | article100 | site_id_2 | <br>| art100 | author100 | PRIMARY | <br>| art100 | author100 | site_id | <br>...<br>+--------------+---------------------------+-----------------+<br>1150 rows in set (1 min 44.23 sec)<br> |
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 |
<br>SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME<br>FROM information_schema.statistics `s`<br> LEFT JOIN information_schema.index_statistics IST<br> ON CONCAT_WS('.', s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME) = IST.INDEX_NAME<br>WHERE IST.INDEX_NAME IS NULL;<br> |
Resources
RELATED POSTS
