Home

List of unindexed foreign key columns in MySQL

2010, February 4 - 17:50 — müzso
The information_schema in MySQL 5.x gives you the opportunity to dig into the database metadata. Unfortunately it's slow like hell. I've attempted to write a query that returns all foreign keys that have no index defined on their columns. It seems that information_schema needs a lot more tuning, because my query results in the following error message:
#126 - Incorrect key file for table '/mnt/mysql-tmp/#sql_969_2.MYI'; try to repair it

Here's the query:
SELECT kc.table_schema, kc.table_name, kc.column_name, tc.constraint_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kc ON tc.constraint_schema = kc.constraint_schema
AND tc.constraint_name = kc.constraint_name
AND tc.table_schema = kc.table_schema
AND tc.table_name = kc.table_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND NOT EXISTS (
        SELECT 1
        FROM information_schema.columns c
        WHERE c.table_schema = kc.table_schema
        AND c.table_name = kc.table_name
        AND c.column_name = kc.column_name
        AND c.column_key != ''
)
ORDER BY kc.table_schema, kc.table_name, kc.column_name

Please, drop a comment if you know how can I make this work. Thx.

Syndicate content