Can MySQL use indexes over 2 different storage engines?

Question

I have a myisam table and a innodb one. The innodb has an index on a foreign key from myisam. Can MySQL use that index when performing joins?

Answer 1

Queries that join tables from multiple storage engines can use indexes from any of the tables.

Note, however, that MyISAM does not support foreign keys, and you cannot create a foreign key that targets a MyISAM table. See the MySQL manual for details on foreign keys.

Answer 2

Yes, it can. Mostly joins will be implemented by doing a normal select from one table (hopefully a key lookup or range scan) and then looking up the joining key on the other table. That second lookup will use an appropriate index on the other table (provided such an index exists and the optimiser decides it's a good idea to use it).

The MySQL storage engine interface allows the server to use indexes from different engines in the same query.

Whether a foreign key constraint exists or not is irrelevant for selects - it will use suitable indexes anyway.