find_unused_index.sql

find_unused_index

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;