If a table fits almost entirely in main memory, the fastest way
to perform queries on it is to use hash indexes.
InnoDB has a mechanism that monitors index
searches made to the indexes defined for a table. If
InnoDB notices that queries could benefit
from building a hash index, it does so automatically.
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 the pattern of searches that
InnoDB observes for 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.
builds hash indexes on demand for those pages of the index that
are often accessed.
In a sense,
InnoDB tailors itself through the
adaptive hash index mechanism to ample main memory, coming
closer to the architecture of main-memory databases.