In our mysql database, we use both myisam and innodb tables, though there are more myisam tables.
- 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?
This question came from our site for professional system and network administrators.
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
ASPECT #3 : Query Optimizer's View of the JOIN
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
InnoDB's index cardinality is NEVER STABLE !!! If you run
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.