How does mysql deal with queries that touches myisam and innodb tables?
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.