Essentially, I am joining two MyISAM tables and have found that the EXPLAIN output for the query does not change regardless of whether I have an index on the foreign key in the parent table. I'm not sure if the index is in fact being used and it simply can't be seen in the EXPLAIN output, or if in fact for some reason the index isn't useful (either in this particular case or in general).
For example:
Table 1:
ex_cat, ex_name
(Primary key on ex_cat.)
Table 2:
cat, ex_cat, data
(Composite primary key on cat, ex_cat; separate
non-unique index on ex_cat?)
I am running the following query:
SELECT *
FROM table1 INNER JOIN table2
ON table1.ex_cat = table2.ex_cat
WHERE table2.cat = 'value'
ORDER BY table2.data;
It gives me the following EXPLAIN output, regardless of whether I have an index on the ex_cat column in table 2 or not:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE table2 ref PRIMARY PRIMARY 22 const 1 Using where; Using filesort
1 SIMPLE table1 eq_ref PRIMARY PRIMARY 32 table2.ext_cat 1 Using where
Which seems right; it's narrowing using the WHERE clause on the parent table primary key, then it's joining the result using the ext_cat column in the parent table and the primary key in the child table. However, since it's the same either way, there's nothing to explicitly show whether there is value in having an index on table2.ext_cat. The tables are so small the queries complete near-instantly either way, so this is more out of academic interest at the moment.
I also tried switching to InnoDB to see if there was any difference in behaviour (not officially using foreign keys, since those would create indexes automatically; just the same test as above) but the explain output was identical.
So, is it indeed improving query performance to have an index on the foreign key? If so, is there some way to 'see' the effect with an EXPLAIN (or otherwise)? And if not, can you explain what about this situation causes it to be unnecessary?
Edit: OK, I expect what's going on is because MySQL is choosing to first narrow using the WHERE
clause, then join the result to the child table, any indexes on the parent table are irrelevant at that point. In cases where it is more efficient to join first, then apply the clause, it would use the foreign key index in the join. Is that correct, and do both behaviors indeed occur depending on the table contents and the query? If not, when are indexes on foreign keys useful?
Edit 2: According to this it would appear the answer is never, if the parent table appears first in the EXPLAIN: mysql-indexes. An index is only used on one half of a join, since it essentially goes one table at a time, joining the result to the next table in the cascade.
Best Answer
Very quick answer:
MyISAM
does not supportFOREIGN KEY
s at all... AnyFOREIGN KEY
you define on aMYISAM
table is silenty thrown away.When you later switch to
InnoDB
(i.e.ALTER TABLE my_table ENGINE=InnoDB
) theFOREIGN KEY
is not there either; it was thrown away when the table used to beMyISAM
.