Hash index and where like clause From Mysql documentation it is clearly said that Hash indexes  are used only for equality comparisons that use the = or &lt;=&gt; operators.

I have a small test table:

`CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=latin1;`


And query: explain select * from test where test.name like 'sun%'
And it shows that it uses index: 
And query: explain extended select * from test where test.name like 'sun%'

But it can not be according to documantion. Mysql version: 5.6.32-78.0.
Any ideas?
Because the table has InnoDB engine and only 2 columns, the one in the index and the PRIMARY KEY column, the index is a "covering" index for the query, so it is used. It's not used as effectively as a B-tree would be used but it still covers the query. Note that any secondary index of an InnoDB table carries the PRIMARY KEY columns data as well, so in this case it's actually (name, id) internally. 

Consider that the optimizer had 2 options, either scan the whole table or scan the whole index. It chose the index because it is less wide (InnoDB tables have a extra "baggage" of 13 bytes per row. For the secondary indexes this baggage is smaller.)

You can check by doing some tests:


Test A: add a third column in the table, without adding any indexes and check the execution plan. It would probably do a table scan and not use the index. The index carries the name and id (primary key) data but not any other column data, so it would not be covering the SELECT * query.
Test B: convert the table to use MyISAM engine and check the execution plan again. Indexes for MyISAM tables do not carry the primary columns, so the plan should again probably be a table scan and not use the index.
Test C/D: same as A or B (add a 3rd column or convert the table to MyISAM) but run the query SELECT name FROM test WHERE name LIKE 'sun%';. The index should be used again as it is covering the query and is cheaper to use than a table scan.