title: mysql difference in index usage between MyISAM and InnoDB
body: <p>I have these small tables, <code>item</code> and <code>category</code>:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I have inserted 100 categories and 1000 items.</p>
  
  <p>If I run this:</p>
  
  <pre><code>EXPLAIN SELECT item.id,category.name AS category_name FROM item JOIN category ON item.category_id=category.id;
  </code></pre>
  
  <p>Then, if the tables' engine is InnoDB I get:</p>
  
  <pre><code>+----+-------------+----------+-------+---------------+-------------+---------+--------------------+------+-------------+
  | 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 |
  +----+-------------+----------+-------+---------------+-------------+---------+--------------------+------+-------------+
  </code></pre>
  
  <p>Whereas, if I switch to MyISAM (with <code>alter table engine=myisam</code>) I get:</p>
  
  <pre><code>+----+-------------+----------+--------+---------------+---------+---------+-------------------------+------+-------+
  | 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 |       |
  +----+-------------+----------+--------+---------------+---------+---------+-------------------------+------+-------+
  </code></pre>
  
  <p>My question is, why this difference in the way indexes are handled?</p>