What is the difference between indexes in MyISAM and InnoDB? myisaminnodb

What is the difference between indexes in MyISAM and InnoDB? myisaminnodb
Two small tables, item and category:

CREATE TABLE `item` (  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,  `name` varchar(150) NOT NULL,  `category_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(150) NOT NULL,  PRIMARY KEY (`id`),  KEY `name` (`name`)) CHARSET=utf8

100 pieces of data are inserted in category, and 1000 pieces are inserted in item.
When the storage engine of the table is InnoDB

EXPLAIN SELECT item.id,category.name AS category_name FROM item JOIN category ON item.category_id=category.id;

Result:

+----+-------------+----------+-------+---------------+-------------+---------+--------------------+------+-------------+| 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 |+----+-------------+----------+-------+---------------+-------------+---------+--------------------+------+-------------+

Switch the storage engine of the table to MyISAM (use 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;

Result:

+----+-------------+----------+--------+---------------+---------+---------+-------------------------+------+-------+| 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 item of MyISAM uses 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 as follows:
In InnoDB, all secondary indexes contain the table's primary key columns. Therefore, the index (name) of the name columns of the two tables implicitly holds two columns: the name of the current field and the id of a primary key.
This means that the EXPLAIN explanation of the access to the category table is "index-scan" (the type is "index", which confirms this ). By scanning the index, it can also access the id column to find related records of the item in the second table.
Similarly, the index in the category_id field of the item table is actually category_id and id, so you only need to read the index to get the item. id. You do not need to read the table (the Extra value is "Using index", which confirms this statement ).
MyISAM does not save the primary key in the secondary index as InnoDB does, so it cannot be optimized similarly. If the access type of the category table is "ALL", a full table scan will be performed.
I expect that the access to the item table of MyISAM is "ref" because it uses the index of the category_id column to find rows. However, when the data volume in the TABLE is small or you have not completed the analyze table item after creating the index, the Optimizer may produce distorted results.
Bill Karwin:
It seems that the table scan optimizer prefers an index scan, so it performs an index scan in InnoDB and puts the category table in front. The optimizer gave up the order of the tables we gave it in the query, and it re-ordered these tables.
In the two tables under the MyISAM engine, no matter who the optimizer chooses to access the table first, it performs a table scan. However, by placing the category table in step 2, it discards the secondary index of the item table, the primary key index of the category table is connected. The optimizer prefers to search for a unique or primary index (type "eq_ref ").
Original article: Workshop.