Trusted answers to developer questions

What is an adaptive hash index in MySQL?

Anjana Shankar

Grokking the Behavioral Interview

Get Educative’s popular interview prep course for free.


Adaptive hash index or AHI allows MySQL InnoDB engine to behave more like in-memory databases. It is a hash index that is built on top of the Btree index, enabling faster look ups.

How it works

The AHI is built by observing the search pattern, using the prefix of the index key to build a hash index. If an entire table is in memory, a hash index can help speed up the query by directly looking up the element.

The AHI would be helpful in speeding up queries that do an exact match and would not be beneficial for queries with LIKE operators and wild card matches.

What is a hash index?

Indexes that use hash tables are generally referred to as hash indexes. Hash tables are extremely efficient for looking up values, which means that queries that look for an exact match can be executed very quickly.

An example of a hash table is shown below. It created a hash table with the department as the hash key.

%0 node_2 Director node_1 Managers node_1_1 Employee2 node_1->node_1_1 node_1_2 Employee3 node_1_1->node_1_2 node_0 Engineering node_0_1 Employee1 node_0->node_0_1

For queries with a where clause that asks for employees belonging to a certain department, the above index would help return the result in constant time.

Performance impact

AHI can be thought of as a cache. If the data is available in the AHI, we improve the performance of database lookups. If, however, the data is not available in the AHI, the performance gets worse.

Maintenance cost

Maintenance cost can be seen in terms of the total number of rows added to or removed from AHI versus successful lookups. A high ratio would mean a lot of look ups, thereby justifying the cost of maintenance of AHI.

Enabling AHI also adds an extra layer of contention. AHI can become a bottleneck at high concurrency, and disabling it might add value to your database.

Enabling or disabling the AHI

The AHI is enabled by default in InnoDB. The variable innodb_adaptive_hash_index can be used to enable or disable the AHI.

You can use --skip-innodb_adaptive_hash_index at the start of the database server to disable it.


Trusted Answers to Developer Questions

Related Tags

Keep Exploring
Related Courses