I'm running a quick query to make sure that all the foreign keys in my table are referencing a field that is indexed.
My code is as follows:
select table_name, column_name, index_name from statistics where table_name in (select referenced_table_name from key_column_usage where table_name='table' and table_schema='schema') and column_name in (select referenced_column_name from key_column_usage where table_name='table' and table_schema='schema') order by table_name;
I'm running this on multiple environments, but for some reason I'm receiving duplicate results on one particular environment. The tables are all set up the same, however, so I'm not sure what is causing this behavior.
Any suggestions?
In that mysql server you probably have 2 databases (schemas) with the same structure or you have multi-column foreign keys. Include the table_schema field in the select list of the outer query to confirm.
Use a multi-column in operator or inner join instead of multiple single column in operators in the where clause of your query.