Clever Geek Handbook

Can MySQL use indexes over two different storage systems?

I have myisam and innodb table. Innodb has an index for the foreign key from myisam. Can MySQL use this index when doing joins?

+2


source to share


2 answers


Queries that combine tables from multiple storage systems 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. For more information on foreign keys, see the user manual .

+3


source


Yes it is possible. Basically joins will be implemented by doing a normal selection from one table (hopefully a key lookup or range scan) and then looking up the join key in another table. This second lookup will use the corresponding index on another table (assuming such an index exists and the optimizer decides it is a good idea to use it).

The MySQL memory engine interface allows the server to use indexes from different engines in a single query.



Whether the foreign key constraint exists or not, it has nothing to do with choices - it will use the appropriate indexes anyway.

0


source






More articles:


All Articles