Skip to content

Instantly share code, notes, and snippets.

@benders
Forked from gnarg/gist:114268
Created May 19, 2009 18:31
Show Gist options
  • Save benders/114299 to your computer and use it in GitHub Desktop.
Save benders/114299 to your computer and use it in GitHub Desktop.
Finds unused indexes in a mysql database
// Finds unused indexes in a mysql database
SELECT
t.TABLE_SCHEMA,
t.TABLE_NAME,
s.INDEX_NAME,
s.COLUMN_NAME,
s.SEQ_IN_INDEX,
( SELECT MAX(SEQ_IN_INDEX)
FROM INFORMATION_SCHEMA.STATISTICS s2
WHERE s.TABLE_SCHEMA = s2.TABLE_SCHEMA
AND s.TABLE_NAME = s2.TABLE_NAME
AND s.INDEX_NAME = s2.INDEX_NAME
) AS `COLS_IN_INDEX`,
s.CARDINALITY AS `CARD`,
t.TABLE_ROWS AS `ROWS`,
ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `SEL %`
FROM INFORMATION_SCHEMA.STATISTICS s
INNER JOIN INFORMATION_SCHEMA.TABLES t
ON s.TABLE_SCHEMA = t.TABLE_SCHEMA
AND s.TABLE_NAME = t.TABLE_NAME
WHERE t.TABLE_SCHEMA != 'mysql'
AND t.TABLE_ROWS > 10
AND s.CARDINALITY IS NOT NULL
AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00
ORDER BY `SEL %`, TABLE_SCHEMA, TABLE_NAME;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment