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.
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.
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.
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.
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 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.
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.