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;
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.