Suggest an edit

Improve this article

Refine the answer for “When are Hash indexes more effective?”. Your changes go to moderation before they’re published.

Approval required

Content

What you’re changing

Title (EN)

Short answer (EN)

Shown above the full answer for quick recall.

Answer (EN)

Hash indexes are more effective when queries look up data **by an exact key match**,
not by ranges or sorting.

### Effective cases

1. **Equality lookups (**`=`**)**
For example:

```sql
  SELECT * FROM users WHERE email = 'test@mail.com';
  ```
Here a Hash index finds the value instantly, computing the hash and jumping straight to the record.
This is faster than a B-Tree, which has to walk several tree levels (`O(log n)`).
2. **Queries with the** `IN` **and** `NOT IN` **operators**

```sql
  SELECT * FROM users WHERE id IN (1, 5, 7, 9);
  ```
Each `id` gets looked up directly by hash, fast and independently.
3. **Tables with a large volume of data and unique keys**
For example, lookups on `UUID`, `email`, `token`, `session_id`,
where values are distributed randomly and a hash index performs better than a tree.
4. **High-frequency point lookups against a structure that rarely changes**
Hash indexes work well in systems where data is read more often than it's written
(e.g. cache tables, lookup tables in analytics, Redis-like scenarios).

### Ineffective cases

- range conditions (`>`, `<`, `BETWEEN`),
- sorting (`ORDER BY`),
- grouping (`GROUP BY`).

A Hash index has no notion of "neighboring" values,
so it can't handle ranges or ordering.

**Summary:**
A Hash index is more effective when you need to **find a specific value by key, fast and exactly**,
not when you need to "compare, sort, or group".

Markdown · drag & drop images · ⌘B / ⌘I shortcuts233 words

For the reviewer

Note to the moderator (optional)

Visible only to the moderator. Helps review go faster.