MySQL Index Guide

Creating Indexes

Basic index creation syntax for various index types.

-- Single-column index
CREATE INDEX idx_users_email ON users(email);

-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Composite index (column order matters!)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Full-text index
CREATE FULLTEXT INDEX idx_articles_body ON articles(title, body);

-- Prefix index (first 10 chars of a long column)
CREATE INDEX idx_url_prefix ON pages(url(10));

-- Drop index
DROP INDEX idx_users_email ON users;

-- Show indexes on a table
SHOW INDEX FROM users\G

Composite & Covering Indexes

Design composite indexes using the leftmost prefix rule. A covering index satisfies a query entirely from the index.

-- Composite index: (status, created_at)
-- Useful for: WHERE status = ? ORDER BY created_at
CREATE INDEX idx_orders_status_date ON orders(status, created_at);

-- Covering index: includes all columns needed by the query
CREATE INDEX idx_covering ON orders(user_id, status, total)
  -- query: SELECT status, total FROM orders WHERE user_id = ?
  -- no table lookup needed (Extra: Using index)

-- Leftmost prefix usage
-- Index: (a, b, c)
-- ✓ WHERE a = 1
-- ✓ WHERE a = 1 AND b = 2
-- ✓ WHERE a = 1 AND b = 2 AND c = 3
-- ✗ WHERE b = 2          (skips leading column)
-- ✗ WHERE b = 2 AND c = 3

-- Range break: stop using index after first range column
-- Index: (status, price, name)
-- ✓ WHERE status = 'A' AND price < 100   (uses status + price)
-- ✗ column 'name' not used after range on price

EXPLAIN Analysis

Use EXPLAIN to inspect query execution plans and identify missing indexes.

-- Basic EXPLAIN
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- EXPLAIN with FORMAT=JSON for more detail
EXPLAIN FORMAT=JSON
SELECT u.name, COUNT(o.id)
FROM users u JOIN orders o ON u.id = o.user_id
GROUP BY u.id\G

-- Key columns to watch:
-- type:  ALL (full scan) → range → ref → eq_ref → const (best)
-- key:   index actually used (NULL = none used)
-- rows:  estimated rows examined
-- Extra: "Using filesort" or "Using temporary" = potential problem

-- EXPLAIN ANALYZE (MySQL 8.0+): runs the query and shows actual costs
EXPLAIN ANALYZE
SELECT * FROM products WHERE category_id = 5 ORDER BY price;

Index Hints

Force, suggest, or ignore specific indexes when the optimizer makes a poor choice.

-- Force an index
SELECT * FROM orders FORCE INDEX (idx_orders_user_date)
WHERE user_id = 42;

-- Suggest an index (optimizer may still ignore)
SELECT * FROM orders USE INDEX (idx_orders_status_date)
WHERE status = 'pending';

-- Ignore an index
SELECT * FROM orders IGNORE INDEX (idx_created_at)
WHERE created_at > '2024-01-01';

-- Force index for ORDER BY
SELECT * FROM products
FORCE INDEX FOR ORDER BY (idx_price)
ORDER BY price ASC LIMIT 10;

Index Best Practices