Is index on foreign key used? (No difference in EXPLAIN.) 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.
Very quick answer: MyISAM does not support FOREIGN KEYs at all... Any FOREIGN KEY you define on a MYISAM table is silenty thrown away.

When you later switch to InnoDB (i.e. ALTER TABLE my_table ENGINE=InnoDB) the FOREIGN KEY is not there either; it was thrown away when the table used to be MyISAM.