Differences in index usage in MyISAM and InnoDB

Two small tables item, Category:

CREATE TABLE ' item ' (  ' id ' mediumint (8) unsigned not NULL auto_increment,  ' name ' varchar (in) NOT NULL,  ' cate gory_id ' Mediumint (8) unsigned not NULL,  PRIMARY key (' id '),  key ' name ' (' name '),  key ' category_id ' (' category_id ') charset=utf8create TABLE ' category ' (  ' id ' mediumint (8) unsigned not NULL auto_increment,  ' name ' varchar () not NULL,  PRIMARY key (' id '),  key ' name ' (' name ')) Charset=utf8

Category Insert 100 data, item insert 1000.
When the table's storage engine executes for InnoDB

EXPLAIN SELECT item.id,category.name as category_name from item JOIN category on Item.category_id=category.id;

Results:

+----+-------------+----------+-------+---------------+-------------+---------+--------------------+------+---- ---------+| ID | Select_type | Table    | type  | possible_keys | key         | key_len | ref                | rows | Extra       |+----+-------------+----------+-------+---------------+-------------+---------+--------------------+ ------+-------------+|  1 | Simple      | category | index | PRIMARY |       name        | 452     | NULL               |  103 | Using Index | |  1 | Simple      | item     | ref   | category_id   | category_id | 3       | dbname.category.id |    5 | Using index |+----+-------------+----------+-------+---------------+-------------+---------+-------------------- +------+-------------+

Then switch the storage engine of the table to MyISAM (using ALTER TABLE ENGINE=MYISAM) or execute

EXPLAIN SELECT item.id,category.name as category_name from item JOIN category on Item.category_id=category.id;

Results:

+----+-------------+----------+--------+---------------+---------+---------+-------------------------+------+-- -----+| ID | Select_type | Table    | type   | possible_keys | key     | key_len | ref                     | rows | Extra |+----+-------------+----------+--------+---------------+---------+---------+-------------------------+--- ---+-------+|  1 | Simple      | item     | All    | category_id   | NULL    | NULL    | NULL                    | 1003 | |       |  1 | Simple      | category | eq_ref | PRIMARY       | PRIMARY | 3       | dbname.item.category_id |    1 |       | +----+-------------+----------+--------+---------------+---------+---------+-------------------------+------+-- -----+

The MyISAM item uses a full table scan. The same data results, the same data, the same index (MyISAM and InnoDB indexes are b-tree by default), why is the difference so big?
Bill Karwin, senior database architect from Schoolmessenger, explains this:
In InnoDB, all two-level indexes contain the table's primary key columns inside. Therefore, the index of the name column of the two tables (name) implicitly holds two columns: A field name and a primary key ID.
This means that EXPLAIN's access to the category table is interpreted as a "index-scan" (The Type "index" confirms this). By scanning the index, it can also access the ID column to find the related record of item in the second table.
Similarly, for an index on the category_id field of the item table is actually a category_id, ID, so simply read the index to get the item.id, there is no need to read the table (the Extra value of "Using index" confirms this statement).
MyISAM does not save the primary key in a level two index like InnoDB, so it cannot be optimized as well. The access type for the category table is "All", which means that a full table scan will be performed.
I expect access to MyISAM's item table to be "ref" because it uses the index of the category_id column to find rows. However, the optimizer may give distorted results when there is less data in the table or if you have not finished ANALYZE table item after you create the index.
Bill Karwin Additional replies:
It looks like the comparison table Scan Optimizer prefers an index scan, so it does an index scan in InnoDB and puts the category table in front. The optimizer discards the order of the tables we gave it in the query, and it reordered the tables.
In the two tables under the MyISAM engine, regardless of the optimizer's choice of first access, a table scan is done, but by placing the category table in the second step, it discards the item table's level two index, which is connected to the primary key index of the category table. The optimizer prefers to find a unique or primary index (type "Eq_ref").
Original link: Http://stackoverflow.com/questions/16024226/mysql-difference-in-index-usage-between-myisam-and-innodb.

Differences in index usage in MyISAM and InnoDB