MySQL Indexing: B-Tree, Hash, and Composite

Learn B-Tree, hash, composite, and covering indexes — when each type helps.

Core

• B-Tree (default): supports =, >, <, BETWEEN, ORDER BY
• Hash: only = (mainly MEMORY engine)
• Composite: multi-column, uses leftmost prefix
• Covering: query served fully from index (no table hit)

Example

-- TABLE
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  status VARCHAR(20),
  created_at DATETIME
);

-- B-TREE INDEX (default)
CREATE INDEX idx_user ON orders(user_id);

-- HASH INDEX (only MEMORY engine)
CREATE TABLE cache (
  id INT,
  value VARCHAR(100),
  INDEX USING HASH (id)
) ENGINE=MEMORY;

-- COMPOSITE INDEX
CREATE INDEX idx_user_status ON orders(user_id, status);

-- COVERING INDEX
CREATE INDEX idx_cover ON orders(user_id, status, created_at);

-- TEST QUERIES

-- Uses B-Tree
EXPLAIN SELECT * FROM orders WHERE user_id = 10;

-- Uses Composite (leftmost prefix)
EXPLAIN SELECT * FROM orders 
WHERE user_id = 10 AND status = 'paid';

-- Not optimal (breaks leftmost rule)
EXPLAIN SELECT * FROM orders WHERE status = 'paid';

-- Uses Covering Index (no table lookup)
EXPLAIN SELECT user_id, status 
FROM orders 
WHERE user_id = 10 AND status = 'paid';

Best Practice

• Default to B-Tree
• Use composite indexes for frequent multi-filters
• Maintain column order (leftmost rule)
• Prefer covering indexes for read-heavy queries
• Avoid over-indexing (write slowdown)
• Index high-cardinality columns
• Always verify with EXPLAIN

Quick Cheat Rule

• Range/sort → B-Tree
• Exact match only → Hash
• Multi-column → Composite
• Fast reads → Covering