A database table has a hash index on the 'email' column. A developer runs the query: SELECT * FROM users WHERE created_at > '2024-01-01'. Why won't the hash index help with this query?
AHash indexes only work on string columns, not date columns
BHash indexes require an exact key value to hash; the ordering information needed to find all dates above a threshold is destroyed by the hash function
CHash indexes are only used when the table has fewer than 1000 rows
DThe hash index would actually work fine for this range query
The hash function maps key values to bucket positions without preserving any sorted order. Two keys adjacent in value (e.g., '2024-01-01' and '2024-01-02') may hash to completely different buckets. To answer a range query like > '2024-01-01', the database would need to scan all buckets — which negates the benefit of the index entirely. A B-tree index, which stores keys in sorted order in its leaf nodes, handles range queries naturally.
Question 2 Multiple Choice
For which of the following query types does a hash index provide a genuine performance advantage over a B-tree index?
ASELECT * FROM orders WHERE amount BETWEEN 100 AND 500
BSELECT * FROM users WHERE name LIKE 'Ali%'
CSELECT * FROM sessions WHERE session_token = 'abc123xyz'
DSELECT * FROM products ORDER BY price ASC
Hash indexes excel at exact equality lookups — they compute a hash of the search key and jump directly to the matching bucket in O(1) average time, avoiding tree traversal. The other three queries all require ordered access: BETWEEN needs a range scan, LIKE 'Ali%' needs prefix matching on sorted keys, and ORDER BY requires sorted output. For all three, the hash's destroyed ordering makes it useless, while a B-tree handles them naturally.
Question 3 True / False
Hash indexes cannot support range queries because the hash function does not preserve the ordering relationship between key values.
TTrue
FFalse
Answer: True
This is the defining limitation of hash indexes. Two keys that are adjacent in sorted order (like 5 and 6, or 'alice' and 'bob') may hash to entirely different buckets, so there is no way to scan 'all keys between X and Y' without checking every bucket. B-trees store keys in sorted order, which is why they handle range queries naturally at the cost of slightly slower equality lookups.
Question 4 True / False
Hash indexes are generally faster than B-tree indexes for most types of database query operations.
TTrue
FFalse
Answer: False
Hash indexes are faster only for equality lookups (WHERE col = value), where they achieve O(1) average time vs. O(log n) for B-tree traversal. For range queries, prefix searches, ORDER BY, and any operation requiring sorted key access, hash indexes provide no benefit and must fall back to a full table scan. B-trees are slightly slower for equality but handle the full range of query types, which is why they are the default index type in most databases.
Question 5 Short Answer
Why can't a hash index answer the query WHERE salary > 50000, even if there is a hash index on the salary column?
Think about your answer, then reveal below.
Model answer: The hash function maps salary values to bucket positions without preserving their numeric order. To find all salaries greater than 50000, you would need to examine all salaries in sorted order starting from 50000. But the hash function may scatter nearby salary values (50001, 50002, 50003) into completely different buckets with no predictable relationship. The only way to answer the query would be to check every bucket, which is equivalent to a full table scan and provides no index benefit.
This is the fundamental tradeoff that makes hash indexes a specialized tool rather than a universal one. They trade ordering for speed on exact matches. Understanding this helps explain why B-trees remain the default: the O(log n) penalty for equality lookups is worth it in exchange for supporting the full range of query types that real applications need.