Take the 2-minute tour ×
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's 100% free, no registration required.

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?
share|improve this question

migrated from serverfault.com Feb 23 '13 at 21:44

This question came from our site for professional system and network administrators.

1 Answer 1

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-UNCOMMITTED and 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.

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 TABLE against 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 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.

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.