As described in Section 7.5, “Controlling Adaptive Hash Indexing”, it may be desirable, depending on your workload, to dynamically enable or disable the adaptive hash indexing scheme InnoDB uses to improve query performance.
Version 5.1.24 of MySQL introduced the start-up option
innodb_adaptive_hash_index that allows the adaptive hash index
to be disabled. It is enabled by default. Starting with
InnoDB Plugin 1.0.3, the parameter can be modified by the
SET GLOBAL command, without restarting the
server. Changing the setting requires the
Disabling the adaptive hash index will empty the hash table immediately. Normal operations can continue while the hash table is emptied, and executing queries that have been using the hash table will access the index B-trees directly instead of attempting to utilize the hash index. When the adaptive hash index is enabled, the hash table will be populated during normal operation.