Understanding Indexes
Speed up queries with proper indexing
Indexes are data structures that speed up data retrieval. Think of them like a book's index.
Creating Indexes
-- Single column index CREATE INDEX idx_users_email ON users(email); -- Composite index (multiple columns) CREATE INDEX idx_orders_user_date ON orders(user_id, created_at); -- Unique index (enforces uniqueness) CREATE UNIQUE INDEX idx_users_email_unique ON users(email); -- Drop an index DROP INDEX idx_users_email;
When to Add Indexes
- Primary keys (automatic)
- Foreign keys used in JOINs
- Columns in WHERE clauses
- Columns in ORDER BY
- Columns with high cardinality (many unique values)
Index Trade-offs
Warning: Indexes speed up reads but slow down writes (INSERT, UPDATE, DELETE). Don't over-index. Profile queries first.