If a table fits almost entirely in main memory, the fastest way to perform queries on it is to use hash indexes rather than B-tree lookups. InnoDB monitors searches on each index defined for a table. If it notices that certain index values are being accessed frequently, it automatically builds an in-memory hash table for that index. Based on the pattern of searches that InnoDB observes, it will build a hash index using a prefix of the index key. The prefix of the key can be any length, and it may be that only a subset of the values in the B-tree will appear in the hash index. InnoDB builds hash indexes on demand for those pages of the index that are often accessed.
The adaptive hash index mechanism allows InnoDB to take advantage of large amounts of memory, something typically done only by database systems specifically designed for databases that reside entirely in memory. Normally, the automatic building and use of adaptive hash indexes will improve performance. However, sometimes, the read/write lock that guards access to the adaptive hash index may become a source of contention under heavy workloads, such as multiple concurrent joins.
You can monitor the use of the adaptive hash index and the
contention for its use in the “SEMAPHORES” section of
the output of the
SHOW ENGINE INNODB STATUS command. If you see many
threads waiting on an RW-latch created in
btr0sea.c, then it might be useful to disable
adaptive hash indexing.
The configuration parameter
innodb_adaptive_hash_index can be
set to disable or enable the adaptive hash index. See
Section 8.3.4, “Dynamically Changing