Geek asks and answers

Mysql differences in index usage between MyISAM and InnoDB

I have these small tables, itemand 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=utf8

CREATE TABLE `category` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) CHARSET=utf8

I inserted 100 categories and 1000 elements.

If I run this:

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

Then, if the InnoDB table engine, I get:

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

If I switch to MyISAM (s alter table engine=myisam), I get:

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

My question is: why is this difference in the way indexes are processed?

+5
source share
1 answer

In InnoDB, any secondary index inside contains a primary key column in the table. Therefore, the index name in the column (name) is implicitly indicated in the columns (name, identifier).

, EXPLAIN " " ( "" ). , id, , item.

(category_id), (category_id, id), item.id , . ( "" " " ).

MyISAM , . - "", .

, MyISAM "ref", , on (category_id). , , ANALYZE TABLE item .

:

, , InnoDB . , .

MyISAM , , , , PRIMARY . ( "eq_ref" ).

+4

More articles:


All Articles