Skip to content

Instantly share code, notes, and snippets.

@qixiaobo
Created February 27, 2019 01:42
Show Gist options
  • Save qixiaobo/f96a655fd9af18b6e9c245422d8c68ef to your computer and use it in GitHub Desktop.
Save qixiaobo/f96a655fd9af18b6e9c245422d8c68ef to your computer and use it in GitHub Desktop.
find_unused_index.sql
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 / t.TABLE_ROWS ) * 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 / t.TABLE_ROWS ) < 0.01
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