What is an Index?
Index Trade-offs
| More Indexes | Fewer Indexes |
|---|---|
| Faster SELECT | Slower SELECT |
| Slower INSERT/UPDATE | Faster INSERT/UPDATE |
2 rows
A shortcut that helps database find data faster, like a book's index.
When to Create Index
Index Trade-offs
| More Indexes | Fewer Indexes |
|---|---|
| Faster SELECT | Slower SELECT |
| Slower INSERT/UPDATE | Faster INSERT/UPDATE |
2 rows
-- Columns you search often
CREATE INDEX idx_email ON users(email);
-- Columns in WHERE clause
CREATE INDEX idx_status ON orders(status);
-- Foreign key columns
CREATE INDEX idx_customer ON orders(customer_id);When NOT to Index
Index Trade-offs
| More Indexes | Fewer Indexes |
|---|---|
| Faster SELECT | Slower SELECT |
| Slower INSERT/UPDATE | Faster INSERT/UPDATE |
2 rows
- Small tables (< 1000 rows)
- Columns rarely searched
- Columns with few unique values (like gender)
The Trade-off
Index Trade-offs
| More Indexes | Fewer Indexes |
|---|---|
| Faster SELECT | Slower SELECT |
| Slower INSERT/UPDATE | Faster INSERT/UPDATE |
2 rows
Summary
Index Trade-offs
| More Indexes | Fewer Indexes |
|---|---|
| Faster SELECT | Slower SELECT |
| Slower INSERT/UPDATE | Faster INSERT/UPDATE |
2 rows
- Index = Faster searches
- Index columns in WHERE and JOIN
- Don't over-index (slows writes)