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 storage engine 1.0.3, the parameter can be modified by the SET GLOBAL command, without restarting the server. Changing the setting requires the SUPER privilege.

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.