SQL Indexes
Indexes are the single most important tool for SQL performance. They're data structures (usually B-trees) that let the database find rows without scanning the entire table. A missing index can make a query 1000× slower.
40 min•By Priygop Team•Last updated: Feb 2026
Index Fundamentals
- B-tree index — The default index type. Efficient for =, <, >, BETWEEN, ORDER BY. Like a book's index — jump directly to the right page
- Primary key — Automatically indexed. Clustered index in MySQL (data stored in PK order). Always use for lookups
- Unique index — Enforces uniqueness + speeds up lookups. CREATE UNIQUE INDEX idx_email ON users(email)
- Composite index — Multiple columns: INDEX(category_id, price). Column order matters — leftmost prefix rule
- Covering index — Contains all columns needed by a query. Database reads only the index, not the table (index-only scan)
- Full-text index — For text search: FULLTEXT INDEX on text columns. Supports MATCH...AGAINST queries
- Cost of indexes — Indexes speed reads but slow writes (INSERT/UPDATE/DELETE must update indexes). Don't over-index
Index & EXPLAIN Code
Example
-- Creating indexes
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(created_at);
CREATE UNIQUE INDEX idx_users_email ON users(email);
-- Composite index (order matters!)
CREATE INDEX idx_products_cat_price ON products(category_id, price);
-- This index helps:
-- WHERE category_id = 5 AND price < 100 ✅
-- WHERE category_id = 5 ✅ (uses leftmost)
-- WHERE category_id = 5 ORDER BY price ✅ (both columns)
-- This index does NOT help:
-- WHERE price < 100 ❌ (skips first column)
-- EXPLAIN: Analyze query execution plan
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
-- Look for:
-- type: 'ref' or 'const' (good) vs 'ALL' (full table scan — bad!)
-- key: which index is used
-- rows: estimated rows scanned (lower = better)
-- Extra: 'Using index' (covering index — best case)
-- EXPLAIN ANALYZE (actual execution stats)
EXPLAIN ANALYZE
SELECT c.name, COUNT(o.id) AS orders
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.created_at >= '2025-01-01'
GROUP BY c.id;
-- Common optimization: avoid SELECT * with covering index
-- Slow: SELECT * FROM products WHERE category_id = 5 ORDER BY price;
-- Fast: SELECT id, name, price FROM products WHERE category_id = 5 ORDER BY price;
-- With INDEX(category_id, price, id, name) — all columns in index!