Joining InnoDB tables with MyISAM tables Joining InnoDB tables with MyISAM tables mysql mysql

Joining InnoDB tables with MyISAM tables


What jumps out immediately at me is MyISAM.

ASPECT #1 : The JOIN itself

Whenever there are joins involving MyISAM and InnoDB, InnoDB tables will end up having table-level lock behavior instead of row-level locking because of MyISAM's involvement in the query and MVCC cannot be applied to the MyISAM data. MVCC cannot even be applied to InnoDB in some instances.

ASPECT #2 : MyISAM's Involvement

From another perspective, if any MyISAM tables are being updated via INSERTs, UPDATEs, or DELETEs, the MyISAM tables involved in a JOIN query would be locked from other DB Connections and the JOIN query has to wait until the MyISAM tables can be read. Unfortunately, if there is a mix of InnoDB and MyISAM in the JOIN query, the InnoDB tables would have to experience an intermittent lock like its MyISAM partners in the JOIN query because of being held up from writing.

Keep in mind that MVCC will still permit READ-UNCOMMITTED and REPEATABLE-READ transactions to work just fine and let certain views of data be available for other transactions. I cannot say the same for READ-COMMITTED and SERIALIZABLE.

ASPECT #3 : Query Optimizer

MySQL relies on index cardinality to determine an optimized EXPLAIN plan. Index cardinality is stable in MyISAM tables until a lot of INSERTs, UPDATEs, and DELETEs happen to the table, by which you could periodically run OPTIMIZE TABLE against the MyISAM tables. InnoDB index cardinality is NEVER STABLE !!! If you run SHOW INDEXES FROM *innodbtable*;, you will see the index cardinality change each time you run that command. That's because InnoDB will do dives into the index to estimate the cardinality. Even if you run OPTIMIZE TABLE against an InnoDB table, that will only defragment the table. OPTIMIZE TABLE will run ANALYZE TABLE internally to generate index statistics against the table. That works for MyISAM. InnoDB ignores it.

My advice for you is to go all out and convert everything to InnoDB and optimize your settings accordingly.

UPDATE 2012-12-18 15:56 EDT

Believe it or not, there is still an open ticket on InnoDB/MyISAM joining during a SELECT FOR UPDATE. If you read it, it sums up the resolution as follows : DON'T DO IT !!!.


I don't think transaction management will work properly or at all since MyISAM tables don't handle it.