MySQL: `SHOW INDEX` shows differently than `SHOW CREATE TABLE`

Question

I'm trying to understand why an index isn't being created the way I expected it. I was in the process of investigating what happened when I created a HASH index on top of an existing foreign key.

create table users (id int, primary key(id));
create table temp (id int not null, primary key(id), user_id int(11) default null);

Right now there is no foreign key yet. show create table temp; outputs what you'd expect:

| temp  | CREATE TABLE `temp` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

And show index from temp:

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| temp  |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Everything is good. Now lets add a FK:

alter table temp add foreign key key_name (user_id) references users (id);

This shows us show create table temp;:

| temp  | CREATE TABLE `temp` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `key_name` (`user_id`),
  CONSTRAINT `temp_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

and show index from temp;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| temp  |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| temp  |          1 | key_name |            1 | user_id     | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

So far so good: we have added a foreign key constraint and MySQL automatically added a BTREE index which it uses to enforce the constraint.

Now I would like that index to be a HASH so I'm adding it:

create index index_name using hash on temp (user_id);

Here's where it becomes strange. You can see the KEY (...) USING HASH entry when you do show create table temp as expected:

| temp  | CREATE TABLE `temp` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`user_id`) USING HASH,
  CONSTRAINT `temp_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

However when you do show index from temp;

+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| temp  |          0 | PRIMARY    |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| temp  |          1 | index_name |            1 | user_id     | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Nothing changed! And the index is still a BTREE!.

What is going on here?

EDIT: someone called to my attention that maybe create index ... isn't the same as alter table add index ... somehow. Well I tried it, and they're certainly doing the same thing. alter table temp add index (user_id) using hash leads to

| temp  | CREATE TABLE `temp` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`user_id`) USING HASH,
  KEY `user_id` (`user_id`) USING HASH,
  CONSTRAINT `temp_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |



+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| temp  |          0 | PRIMARY    |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| temp  |          1 | index_name |            1 | user_id     | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| temp  |          1 | user_id    |            1 | user_id     | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Answer 1

The InnoDB storage engine only supports BTREE indexes, not HASH indexes.

When you tried to create a HASH index, it silently converted the index to BTREE.