In our mysql database, we use both myisam and innodb tables, though there are more myisam tables.
QUESTIONS
- If a query involves both myisam and innodb tables, will the query use table lock or row lock on innodb tables?
- Is this a good practice?
Mysql – How does thesql deal with queries that touches theisam and innodb tables
innodbjoin;myisamMySQL
Related Question
- Mysql – How to copy a MySQL database which is a mixture of InnoDB and MyISAM to the same server
- MySQL – Choosing Between MYISAM and INNODB Engines
- Mysql – Large queries on MyISAM tables crashing MySQL
- Mysql – Complex query performance in MySQL InnoDB versus MyISAM
- Mysql – InnoDB MySQL table with a trigger that inserts into a MyISAM table
- Xtrabackup – How to Make Xtrabackup Ignore MyISAM Tables and Backup Only InnoDB
Best Answer
There are three aspects to doing a JOIN query that is can be alarming
ASPECT #1 : Locking Behavior
Whenever there are joins involving MyISAM and InnoDB, InnoDB tables will end up behaving like MyISAM in terms of table-level locking instead of its normal row-level locking.MVCC and ACID compliance cannot be applied to the MyISAM data. It could possibly be stagnated for InnoDB tables as well.
ASPECT #2 : MyISAM's Involvement
If any MyISAM table is being updated via INSERTs, UPDATEs, or DELETEs, the MyISAM table involved in a JOIN query would be locked from other DB Connections, forcing the JOIN query to wait until the MyISAM table can be read. In light of this, if there is a mix of InnoDB and MyISAM in a JOIN query, the InnoDB tables would be subject to intermittent locking of PRIMARY KEY entries (in the Clustered Index).
Keep in mind that MVCC will still allow
READ-UNCOMMITTEDandREPEATABLE-READtransactions to work just fine and let certain views of data be available for other transactions. The same cannot be said for READ-COMMITTED and SERIALIZABLE transactions.ASPECT #3 : Query Optimizer's View of the JOIN
MyISAM
MySQL will rely on the index cardinality of tables to determine an optimized EXPLAIN plan. Index cardinality is usually stable for a MyISAM table until it is inundated with INSERTs, UPDATEs, and DELETEs. Consequently, you will needs to periodically run
OPTIMIZE TABLEagainst the MyISAM table.InnoDB
InnoDB's 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 runOPTIMIZE TABLEagainst an InnoDB table, that will only defragment the table.OPTIMIZE TABLEwill runANALYZE TABLEinternally to generate index statistics against the table. That works for MyISAM. InnoDB would usually ignore it. You could disable innodb_stats_on_metadata to get stable EXPLAIN plans, but that would just cause you to do ANALYZE TABLE maintenance on InnoDB tables as much as you would a MyISAM table.EPILOGUE
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 JOINs THAT MIX InnoDB and MyISAM, please.