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.
The start-up option
allows the adaptive hash index to be disabled. It is enabled by
default. You can modify this parameter through the
GLOBAL statement, without restarting the server.
Changing the setting requires the
Disabling the adaptive hash index empties the hash table immediately. Normal operations can continue while the hash table is emptied, and executing queries that were using the hash table access the index B-trees directly instead. When the adaptive hash index is re-enabled, the hash table is populated again during normal operation.