Hash Indexes

Hash Indexes and the Adaptive Hash Index

A hash index maps a key to a bucket: one probe for =, but no order, so no ranges, prefixes or sorting. Only MEMORY and NDB tables build them:

A prefix search cannot use a MEMORY hash indexSQL

CREATE TABLE email_lookup (email VARCHAR(255) NOT NULL, id INT UNSIGNED NOT NULL,
  INDEX USING HASH (email)) ENGINE = MEMORY;
INSERT INTO email_lookup SELECT email, id FROM customers;
EXPLAIN SELECT id FROM email_lookup WHERE email LIKE 'chen%'\G

Output

Query OK, 0 rows affected (0.014 sec)
Query OK, 8 rows affected (0.007 sec)
Records: 8  Duplicates: 0  Warnings: 0
*************************** 1. row ***************************
EXPLAIN: -> Filter: (email_lookup.email like 'chen%')  (cost=3.4 rows=1)
    -> Table scan on email_lookup  (cost=3.4 rows=8)
1 row in set (0.000 sec)

Equality does use the hash index (Index lookup on email_lookup using email). InnoDB given USING HASH builds a B+tree instead, with note 3502. Its own adaptive hash index hashes hot B+tree pages in memory to speed repeated equality lookups, but its latches contend under concurrent joins and scans, so MySQL 8.4 524 turned it off by default (@@innodb_adaptive_hash_index is 0 here). Being server-wide, it was not benchmarked here.

MENU

Hash Indexes