What is the difference between B-tree and Hash indexes in MySQL?

B-tree index (default): a balanced tree data structure where all leaf nodes are at the same depth. Supports: equality lookups (=), range queries (BETWEEN, >, <), prefix LIKE ("abc%"), and ORDER BY (can avoid filesort). Supports composite indexes with leftmost prefix rule. Used by virtually all MySQL index types (InnoDB, MyISAM, Memory engine). Hash index: applies a hash function to the indexed column value to directly locate the data. Supports only exact equality lookups (=, <=>). Does NOT support range queries, ORDER BY, or prefix matching. Much faster for exact lookups (O(1) vs O(log n) for B-tree) when data fits in memory. In MySQL, the Memory storage engine supports hash indexes. InnoDB has an Adaptive Hash Index (AHI): it automatically builds an in-memory hash index on hot B-tree pages when it detects the same pages being accessed repeatedly — you cannot manually create hash indexes in InnoDB. NDB Cluster (MySQL Cluster) supports explicit hash indexes. For most purposes, B-tree indexes are the right choice because of their versatility. If you need O(1) exact lookups and your dataset fits in memory, consider Redis instead.