CSE Roadmap

Common tier · about 2 hours · 5 parts

Part 4 of 5 · 2 min read

Why hash indexes cannot support range queries

With no ordering between buckets, there is no way to walk forward through a range.

Why hash indexes cannot support range queries

Think of the coat-check hooks again: hook 7 and hook 8 have no relationship to each other at all. Knowing where ticket 100 landed gives no hint about where ticket 101 landed.

In plain words: a hash function is designed to scatter keys across buckets as evenly as possible, which means two keys that are close together in value, such as consecutive dates, usually land in completely unrelated buckets. A B+-tree's sorted leaf chain, covered on the previous page, has no equivalent here: there is no way to start at one bucket and walk forward to find the next-highest key, because bucket order carries no meaning. A query such as a BETWEEN range therefore gets no benefit at all from a hash index, and still needs a full table scan.

A worked example. WHERE placed_on BETWEEN '2026-01-01' AND '2026-01-31' against a hash index on placed_on cannot use the index at all, since consecutive dates hash to unrelated, scattered buckets.

The key lines.

SELECT * FROM orders WHERE placed_on BETWEEN '2026-01-01' AND '2026-01-31';
-- a hash index on placed_on gives no benefit here; a full scan still runs

Range query (versus hash index)

A range query asks for all values within a span, such as BETWEEN two dates; a hash index cannot support this, since hashing scatters nearby values into unrelated buckets with no usable order between them.

Example: the scattered, meaningless order of coat-check hook numbers has no way to help find "every ticket between 100 and 110."

Remember this

Choose a hash index only when a column is queried purely by exact match, never by range. For any column that might need BETWEEN, sorting, or </> comparisons, a B+-tree is the right default.

Quick notes

  • A hash index maps keys to buckets using a hash function, reaching a bucket directly without tree traversal.
  • A well-sized hash index answers exact-match lookups in roughly constant time, regardless of table size.
  • A collision happens when two keys share a bucket, requiring a short check within that bucket.
  • A hash index cannot support range queries at all, since hashing destroys any ordering between keys.
  • A B+-tree remains the right default whenever a column might need range queries, not just exact matches.