Joining InnoDB tables with MyISAM tables

Question

We have a set of tables which contain the meta level data like organizations, organization users, organization departments etc. All these table are going to be read heavy with very few write operations. Also, the table sizes would be quite small (maximum number of records would be around 30K - 40K)

Another set of table store OLTP data like bill transactions, user actions etc which are going to be both read and write heavy. These tables would be quite huge (around 30 Million records per table)

For the first set of tables we are planning to go with MyISAM and for the second ones with InnoDb engine. Many of our features would also require JOINS on tables from these 2 sets.

Are there any performance issues in joining MyISAM tables with InnoDB tables? Also, are there any other possible issues (db backups, tuning etc) we might run into with this kind of design?

Any feedback would be greatly appreciated.

Answer 1

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 !!!.