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?