B-Tree versus Hash indexes

WHAT THIS TESTS The interviewer checks whether you understand the structural difference and can match it to access patterns instead of reciting that hash is faster.

A GOOD ANSWER COVERS A B-Tree is a balanced, ordered tree where leaf nodes hold keys in sorted order linked together. Because keys are sorted, it supports equality lookups, range queries like BETWEEN and greater-than, anchored prefix LIKE, MIN and MAX, and ORDER BY without a separate sort, with logarithmic search cost. It is the general-purpose default. A hash index applies a hash function to the key and stores entries in buckets, giving average constant-time equality lookups. But the hash destroys ordering, so it cannot serve range queries, prefix matches, or sorted output, and worst-case performance suffers under collisions or poor hash distribution. Hash indexes also historically lacked crash-safety or replication support in some engines, narrowing their use. In practice B-Trees handle equality well enough that hash indexes are reserved for pure equality lookups on large tables where the marginal lookup speed matters.

COMMON WRONG ANSWERS Recommending hash for range queries or ORDER BY; the structure simply cannot do it. Claiming hash is always faster for equality, ignoring B-Tree caching and collision risk. Forgetting hash gives no prefix matching.

LIKELY FOLLOW-UPS Why is a B-Tree the default; when does hash actually win; what happens to a hash index under heavy collisions; can a hash index be unique.

ONE CONCRETE EXAMPLE A session table queried only by exact session_id can use a hash index for fast equality. But an orders table queried with WHERE created_at BETWEEN two dates ORDER BY created_at needs a B-Tree, because the hash index cannot scan ranges or return sorted rows and would force a full scan plus sort.