What Are Hash Indexes?
You'll learn: What hash indexes are, how they differ from B-tree indexes, and when they're most useful in database systems.
Core Idea
A hash index uses a hash function to map key values directly to storage locations (buckets), allowing extremely fast lookups when you search for an exact value. Unlike B-tree indexes which maintain sorted order and support range queries, hash indexes are optimized for equality comparisons only.
How Hash Indexes Work
Think of a hash index like a library's call number system. Instead of browsing shelves in order (like B-trees), you compute a specific location from the book's ID and go directly there.
When you insert a row:
- The hash function takes the indexed column's value
- Computes a hash code (a number)
- Maps that to a bucket location
- Stores a pointer to the actual row there
For lookups with exact matches (WHERE user_id = 12345), the database:
- Hashes the search value (12345)
- Goes directly to that bucket
- Returns the result in O(1) time — essentially constant speed
Hash vs B-Tree
Hash indexes excel at:
- Exact equality searches (
=) - Very fast single-value lookups
- Simple key-value scenarios
Hash indexes cannot:
- Support range queries (
>,<,BETWEEN) - Maintain sorted order
- Efficiently handle
LIKEpatterns or prefix matching
B-tree indexes remain the default because they support both equality and range operations, while hash indexes are specialized tools for specific use cases.
When to Use Hash Indexes
Hash indexes shine in scenarios like:
- Distributed systems using hash partitioning
- In-memory databases with frequent exact-match lookups
- Primary key or unique constraint lookups where ranges aren't needed
Key Takeaway: Hash indexes provide O(1) exact-match lookups but sacrifice the range-query capabilities and ordered structure that B-tree indexes provide — use them when you need blazing-fast equality searches and never need ranges.