MySQL: Script to find Missing Indexes for particular type of Columns

In this post, I am sharing a script to find missing indexes in MySQL only for those columns which name ended with ‘_id’.

The full table scanning is always creating a performance issue for any database.

As a Database Professional, you might be adding database indexes on a table but periodically the volume of data is changing so we need to analyze the old indexes, or we should find missing indexes which may require for query optimizer.

On the other hand, Database Administrator may also require a report on missing indexes which they can share with developers and users so that they can modify it.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SELECT t.TABLE_SCHEMA ,t.TABLE_NAME ,c.COLUMN_NAME ,IFNULL(kcu.CONSTRAINT_NAME, 'Not indexed') AS Indexed FROM information_schema.TABLES as t INNER JOIN information_schema.COLUMNS as 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 as 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');