Indexing Foreign Keys
InnoDB automatically indexes the primary key but does NOT automatically index foreign key columns on the child table. Missing indexes cause slow JOINs and deletes.
MySQL
Intermediate
4 min read
Indexing Foreign Keys
Example
-- Check for missing FK indexes
SELECT fk.TABLE_NAME, fk.COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE fk
LEFT JOIN information_schema.STATISTICS s
ON s.TABLE_NAME = fk.TABLE_NAME
AND s.COLUMN_NAME = fk.COLUMN_NAME
WHERE fk.REFERENCED_TABLE_NAME IS NOT NULL
AND s.INDEX_NAME IS NULL; Pro Tip
Always add an index on FK columns in child tables.