Hash index and where like clause

Question

From Mysql documentation it is clearly said that Hash indexes are used only for equality comparisons that use the = or <=> 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?

Answer 1

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.

Answer 2

You are right in stating that indexes are used according to the documentation, but MySQL also optimizes much of the queries based on the available indexes.

The documentation for 9.2.1.2 How MySQL Optimizes WHERE Clauses states that:

...Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan...

At the top of the article you will also find the note

Because work on the MySQL optimizer is ongoing, not all of the optimizations that MySQL performs are documented here.

And even the index creation is modified as can be seen in the documentation at 9.2.1.7 Use of Index Extensions

InnoDB automatically extends each secondary index by appending the primary key columns to it

Your quote from the documentation (9.3.8 Comparison of B-Tree and Hash Indexes) is correct but for under the hood optimzations.

If you force the query to not use the index with this:

select * from test ignore index (name) where test.name like 'sun%'

...you will see that the index is well and truly not used.

In the documentation for the EXPLAIN command here 9.8.2 EXPLAIN Output Format they write for the Extra information USING WHERE that:

The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.

Edit: I would summarize these findings as:

Even though MySQL should not optimize a SELECT statement referencing a column with a HASH index, it will up to a certain level depending on under the hood optimizations.

Answer 3

Engines InnoDB and MyISAM have BTree, FULLTEXT, and Spatial indexes. Neither Engine supports HASH.

BTree is nearly as good as HASH for a "point query" (looking up one row, given a key).

BTree is good at range queries; HASH is useless for such. LIKE 'abc%' is a "range" query.

HASH is accepted in the syntax, but it gave you BTree instead.

InnoDB, when building a secondary index (as with your name index), appends the columns of the PRIMARY KEY. This happens to make it a "covering index" (as indicated by "Using index").

Except for Spatial an InnoDB, all of my statements above have applied to all versions of MySQL since InnoDB was added about 15 years ago; they apply to all variants (eg, MariaDB, Percona) and all versions for the foreseeable future.

Your query is optimized as well it it can be. The page you found on HASH is misleading.