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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
There are three aspects to doing a JOIN query that is can be alarming
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.
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
REPEATABLE-READ transactions 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.
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 TABLE against the MyISAM table.
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 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 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.
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.