Indexing and Performance
Learn how to optimize database performance with proper indexing and query optimization techniques.
What is an Index?
An index is a data structure that improves the speed of data retrieval operations on a database table. Think of it like the index at the back of a book - it helps you find information quickly without scanning every page.
Creating Indexes
Single Column Index
-- Speed up searches on email
CREATE INDEX idx_email ON users(email);Composite Index
-- Index on multiple columns
CREATE INDEX idx_name_age ON users(name, age);Unique Index
-- Ensure email uniqueness
CREATE UNIQUE INDEX idx_unique_email ON users(email);When to Create Indexes
Create indexes on columns that are frequently used in:
- WHERE clauses
- JOIN conditions
- ORDER BY clauses
- GROUP BY clauses
-- Good candidates for indexing
SELECT * FROM users WHERE email = '[email protected]';
SELECT * FROM orders WHERE user_id = 123;
SELECT * FROM products ORDER BY category, price;Viewing Indexes
-- SQLite
PRAGMA index_list(users);
-- PostgreSQL
\d users
-- MySQL
SHOW INDEX FROM users;Query Optimization
Use Specific Columns
-- Instead of this
SELECT * FROM users WHERE name = 'John';
-- Use this
SELECT id, name, email FROM users WHERE name = 'John';Avoid Functions on Indexed Columns
-- Slow (function prevents index usage)
SELECT * FROM users WHERE UPPER(name) = 'JOHN';
-- Fast (uses index)
SELECT * FROM users WHERE name = 'john' COLLATE NOCASE;Use LIMIT for Large Results
-- Instead of returning all rows
SELECT * FROM users WHERE age > 30;
-- Limit the result set
SELECT * FROM users WHERE age > 30 LIMIT 100;Performance Analysis
EXPLAIN Query Plan
-- See how the query will be executed
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
-- More detailed analysis
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = '[email protected]';Common Performance Issues
Full Table Scan
Occurs when the database must examine every row in a table:
-- Likely causes full table scan without proper index
SELECT * FROM users WHERE name LIKE '%john%';Missing Indexes
-- Slow without index on user_id
SELECT * FROM orders WHERE user_id = 123;
-- Fast with index
CREATE INDEX idx_user_id ON orders(user_id);Over-Indexing
Too many indexes can slow down INSERT/UPDATE operations:
-- Don't index everything
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_age ON users(age);
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_created_at ON users(created_at);
-- Better: create composite indexes for common query patterns
CREATE INDEX idx_email_age ON users(email, age);Next Steps
Learn Best Practices for writing clean, maintainable SQL code.
Last updated on