GNU Info

(mysql.info)InnoDB Adaptive hash

---

Next: InnoDB Physical record Prev: InnoDB Insert buffering Up: Table and index

---

Enter node , (file) or (file)node

Adaptive hash indexes
.....................

If a database fits almost entirely in main memory, then the fastest way
to perform queries on it is to use hash indexes. InnoDB has an
automatic mechanism which monitors index searches made to the indexes
defined for a table, and if InnoDB notices that queries could benefit
from building of a hash index, such an index is automatically built.

But note that the hash index is always built based on an existing
B-tree index on the table. InnoDB can build a hash index on a prefix of
any length of the key defined for the B-tree, depending on what search
pattern InnoDB observes on the B-tree index.  A hash index can be
partial: it is not required that the whole B-tree index is cached in
the buffer pool. InnoDB will build hash indexes on demand to those
pages of the index which are often accessed.

In a sense, through the adaptive hash index mechanism InnoDB adapts
itself to ample main memory, coming closer to the architecture of main
memory databases.