Spotting missing indexes for MariaDB & MySQL

Here’s a query I use for MySQL / MariaDB to spot any columns that might need indexing.  It uses a bunch of information_schema views to flag any columns that end with the characters “id” that are not indexed. This tests that the column is located at the head of the index through the ORDINAL_POSITION clause. So if it’s in an index at position 2, or higher, this won’t count.

SELECT t.TABLE_SCHEMA, t.TABLE_NAME, c.COLUMN_NAME, IFNULL(kcu.CONSTRAINT_NAME, 'Not indexed') AS `Index`
FROM information_schema.TABLES t
INNER JOIN information_schema.`COLUMNS` c
	ON c.TABLE_SCHEMA = t.TABLE_SCHEMA
	AND c.TABLE_NAME = t.TABLE_NAME
	AND c.COLUMN_NAME LIKE '%Id'
LEFT JOIN information_schema.`KEY_COLUMN_USAGE` kcu
	ON kcu.TABLE_SCHEMA = t.TABLE_SCHEMA
	AND kcu.TABLE_NAME = t.TABLE_NAME
	AND kcu.COLUMN_NAME = c.COLUMN_NAME
	AND kcu.ORDINAL_POSITION = 1	
WHERE kcu.TABLE_SCHEMA IS NULL
AND t.TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql');

The idea here is that any columns that are named business_key_id should probably be indexed. This will give us a list of columns that we can consider for indexing. As usual, your mileage may vary according to your own database design. Do you have any better ideas?


Leave a Reply