Joining InnoDB tables with MyISAM tables?

What jumps out immediately at me is MyISAM Whenever there are joins involving MyISAM and InnoDB, InnoDB tables will end up having table-level locks instead of row-level locking because of MyISAM's involvement in the query and MVCC cannot be applied to the MyISAM data 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 InnoDB and MyISAM in the JOIN query, the InnoDB tables would have to experience a table lock like its MyISAM partners in the JOIN query Another aspect to consider is the Query Optimizer. MySQL relies on index cardinality to determine an optimized query plan.

Index cardinality is stable in MyISAM tables until a lot of INSERTs, UPDATEs, and DELETEs happen to the table, by which you could periodcially 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 MySQL will do dives into the index to estimate the cardinality. Even if you run OPTIMIZE TABLE against an InnoDB table, that will only defrag 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.

What jumps out immediately at me is MyISAM. Whenever there are joins involving MyISAM and InnoDB, InnoDB tables will end up having table-level locks instead of row-level locking because of MyISAM's involvement in the query and MVCC cannot be applied to the MyISAM data. 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 InnoDB and MyISAM in the JOIN query, the InnoDB tables would have to experience a table lock like its MyISAM partners in the JOIN query. Another aspect to consider is the Query Optimizer. MySQL relies on index cardinality to determine an optimized query plan.

Index cardinality is stable in MyISAM tables until a lot of INSERTs, UPDATEs, and DELETEs happen to the table, by which you could periodcially 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 MySQL will do dives into the index to estimate the cardinality. Even if you run OPTIMIZE TABLE against an InnoDB table, that will only defrag 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.

Thanks for the detailed explanation Rolando. If InnoDb tables will also suffer from table level locks while joining with MyISAM tables, then that would impact our OLTP transactions significantly. We will go ahead with InnoDB only.

Thanks again. – Pigol Mar 29 at 21:03.

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

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.