MySQL: Find missing indexes

Foreign-key columns that lack an index are one of the most common and silent performance killers in a MySQL database. Joins slow down, nested loops explode in cost, and the query plan gives you no obvious warning. Run the query below against any schema and you will get a full list of every *_id column that is sitting without an index.

This is especially useful after a schema review, an ORM migration, or when onboarding to an unfamiliar codebase where index discipline may have been uneven.

---

The problem

ORMs like Entity Framework Core create foreign-key columns reliably but do not always create the corresponding index. MySQL does not enforce indexes on foreign-key columns the way it enforces them on primary keys. The result is that order_id, customer_id, and product_id columns accumulate in your schema with no index, and every join or filtered lookup against them performs a full or partial table scan.

---

The fix

Run this against the schema you want to audit. It finds every column whose name ends in _id and reports whether that column is covered by an index.

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'
         );

The ordinal_position = 1 filter is important. An index only helps a join or a predicate on a column if that column is the leftmost key in the index definition. A column that appears second or third in a composite index does not count as indexed for this purpose and will still show up in the results.

Note: The original query had a bug in the LEFT JOIN: kcu.column_name = t.column_name was joining against the table row rather than the column row. The corrected version above uses kcu.column_name = c.column_name, which is what was intended.

---

When to use this

This query is a diagnostic, not a substitute for a proper index review. Use it in the following situations:

• After running migrations on a production or staging database to catch any missing indexes the migration did not generate.
• During a performance investigation where slow join queries are surfacing in the slow query log or EXPLAIN output.
• As part of a periodic database health check, especially on databases that grow through ORM-driven schema changes rather than hand-written DDL.
• When taking over an inherited codebase and needing a quick baseline of the index landscape.

Once you have the list, evaluate each result in context. Not every *_id column warrants its own index; columns on small lookup tables, columns that are never used in a join or WHERE clause, and columns already covered by an existing composite index may not need one. The query tells you what is missing; your query plans and workload tell you what actually matters.

Found this useful? Drop a comment below if you have a variation or ran into an edge case I did not cover.