Comparison between InnoDB and MyISAM on join

For details, see this article:

Http://www.mysqlperformanceblog.com/2006/05/29/join-performance-of-myisam-and-innodb/

 

(1) the connection between InnoDB on the primary key is faster, because for InnoDB, data and indexes are clustered into one block. When the index is read, the data is also read.

(2) InnoDB automatically creates adaptive hash indexes based on query conditions, which is faster than a single btree index.

(3) MyISAM uses key compress to compress the index of character columns, which is slower for random search.

(4) because of its simplicity, MyISAM has lower overhead than InnoDB.

(5) MyISAM's join on the primary key is faster than other keys, because during join scanning, only a precise row can be found for the primary key; for other keys, because you are not sure whether there are more rows, You need to query whether there are the next row, which leads to more overhead.

 

In general, InnoDB performs better on join.