This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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.
1234567891011121314151617 SELECTt.TABLE_SCHEMA,t.TABLE_NAME,c.COLUMN_NAME,IFNULL(kcu.CONSTRAINT_NAME, 'Not indexed') AS IndexedFROM information_schema.TABLES as tINNER JOIN information_schema.COLUMNS as cON c.TABLE_SCHEMA = t.TABLE_SCHEMAAND c.TABLE_NAME = t.TABLE_NAMEAND c.COLUMN_NAME LIKE '%_id'LEFT JOIN information_schema.KEY_COLUMN_USAGE as kcuON kcu.TABLE_SCHEMA = t.TABLE_SCHEMAAND kcu.TABLE_NAME = t.TABLE_NAMEAND kcu.COLUMN_NAME = c.COLUMN_NAMEAND kcu.ORDINAL_POSITION = 1WHERE kcu.TABLE_SCHEMA IS NULLAND t.TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql');