Course contentsShow
← Computer Science
Lesson 1693 of 2,87238. Database Transactions and IndexingFree lesson

Range Query Limitations

Learn why hash indexes cannot efficiently support range queries, ordering, or sorted retrieval unlike B-tree indexes.

Range Query Limitations

What you'll learn: You'll understand why hash indexes fail at range queries while B-tree indexes excel at them.

Why Hash Indexes Struggle with Ranges

Hash indexes are built for exact-match lookups. When you search for a specific key, the hash function computes a bucket location instantly—giving you that coveted O(1) performance. But here's the problem: hash functions scramble data.

Think of it like organizing books by a hash of their ISBN numbers. Book #12345 might land in bucket 789, while book #12346 (just one number away) lands in bucket 42. The books are scattered seemingly randomly across storage, with no relationship between similar keys.

What This Means for Range Queries

When you need to find all records where age BETWEEN 25 AND 30 or retrieve users in alphabetical order from 'Smith' to 'Taylor', a hash index is useless:

  • No ordering preserved: Keys that are numerically or alphabetically close are scattered across different buckets
  • No efficient traversal: There's no way to "walk" from one value to the next in sorted order
  • Full scan required: The database must check every bucket or fall back to scanning the entire table

B-Trees to the Rescue

B-tree indexes maintain sorted order inherently. All keys are stored in a structured, ordered tree where:

  • Similar values cluster together
  • You can efficiently find a starting point and scan forward
  • Range queries just traverse the tree nodes sequentially
  • ORDER BY operations use the existing sort order

Key Takeaway: Hash indexes sacrifice range query support and sorted retrieval for ultra-fast exact lookups—choose B-tree indexes when you need ranges, sorting, or pattern matching; choose hash indexes only when exact-match speed is paramount and you'll never query ranges.