Unused indexes by single query
Posted by Vadim |
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
SQL:
-
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;
SQL:
-
+--------------+---------------------------+-----------------+
-
| 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:
SQL:
-
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;
del.icio.us
digg