What Are MySQL Indexes
An index is a separate data structure that speeds up table lookups. Without an index MySQL does a Full Table Scan — O(n). With the right index, lookup is O(log n).
Index Types
| Type | Description | When to Use |
|---|---|---|
PRIMARY KEY | Unique, NOT NULL, one per table | Row identifier |
UNIQUE | Prevents duplicates | Email, username |
INDEX / KEY | Regular non-unique index | WHERE, ORDER BY, JOIN columns |
FULLTEXT | Full-text search | Text search (better than LIKE) |
Create Indexes
ALTER TABLE orders ADD INDEX idx_status (status);
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);
CREATE INDEX idx_user_status ON orders(user_id, status);Composite Index Rule
Index (a, b, c) works for: WHERE a, WHERE a AND b. Does NOT work for: WHERE b or WHERE c alone — the first column must be in the condition.
Analyze with EXPLAIN
EXPLAIN SELECT * FROM orders WHERE user_id = 5 AND status = 'pending'\G
-- Good: type=ref or type=eq_ref
-- Bad: type=ALL (full table scan)Do not index every column — each index slows down INSERT/UPDATE/DELETE and uses disk space. Only add indexes that demonstrably speed up queries.