Btree vs Hash Index in MySQL: Key Differences and Usage

In MySQL, a Btree index organizes data in a balanced tree structure, supporting range queries and ordered scans efficiently. A Hash index uses a hash table for fast equality lookups but does not support range queries or ordering.

Quick Comparison

This table summarizes the main differences between Btree and Hash indexes in MySQL.

Factor	Btree Index	Hash Index
Structure	Balanced tree	Hash table
Supported Queries	Equality and range queries	Equality queries only
Ordering Support	Yes, supports ORDER BY	No ordering support
Storage Engines	Default for InnoDB and MyISAM	Used mainly by MEMORY engine
Performance	Good for most queries, especially range	Very fast for exact matches
Use Case	General purpose indexing	Specialized for fast exact lookups

Key Differences

Btree indexes store data in a sorted, balanced tree structure. This allows MySQL to quickly find values by traversing the tree and supports both exact matches and range queries like <, >, BETWEEN. Because the data is sorted, Btree indexes also support ORDER BY operations efficiently.

Hash indexes use a hash function to map keys to locations in a hash table. This makes equality lookups extremely fast because MySQL can jump directly to the matching entry. However, hash indexes do not store data in order, so they cannot support range queries or ORDER BY operations.

In MySQL, Btree indexes are the default for most storage engines like InnoDB and MyISAM. Hash indexes are mainly used by the MEMORY storage engine and are limited in flexibility. Choosing between them depends on the query patterns and storage engine used.

Code Comparison

Creating and using a Btree index on a table for a column to speed up range and equality queries.

mysql

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  salary INT,
  INDEX idx_salary (salary) -- Btree index by default
);

-- Query using the Btree index
SELECT * FROM employees WHERE salary BETWEEN 50000 AND 100000 ORDER BY salary;

Output

Returns all employees with salary between 50000 and 100000 sorted by salary.

Hash Index Equivalent

Creating and using a Hash index in MySQL MEMORY engine for fast equality lookups.

mysql

CREATE TABLE session_data (
  session_id CHAR(32) NOT NULL,
  user_id INT,
  data TEXT,
  PRIMARY KEY (session_id)
) ENGINE=MEMORY;

-- Hash index is default for MEMORY engine's primary key

-- Query using the hash index
SELECT * FROM session_data WHERE session_id = 'abc123def456ghi789jkl012mno345pq';

Output

Returns the session data for the exact session_id quickly.

When to Use Which

Choose Btree indexes when you need to support a wide range of queries including range searches, sorting, and prefix matching. They are versatile and work well with InnoDB and MyISAM tables.

Choose Hash indexes only when you have exact equality lookups on MEMORY engine tables and need the fastest possible access for those queries. Avoid hash indexes if you require range queries or ordering.

Key Takeaways

Btree indexes support both equality and range queries with ordered data storage.

Hash indexes provide very fast equality lookups but do not support range or order operations.

Btree is the default and most versatile index type in MySQL for InnoDB and MyISAM.

Hash indexes are mainly used in MEMORY engine tables for quick exact-match lookups.

Choose the index type based on your query patterns and storage engine requirements.