You are viewing a free preview of this lesson.
Subscribe to unlock all 10 lessons in this course and every other course on LearningBro.
As your database grows, query performance becomes critical. This lesson covers the fundamentals of database indexes and how to write efficient SQL.
An index is a data structure that speeds up data retrieval — like a book's index that lets you jump straight to the right page instead of reading every page.
Without an index, a query must scan every row (full table scan). With an index on the searched column, the database jumps directly to matching rows.
Trade-off: Indexes speed up reads but slow down writes (INSERT/UPDATE/DELETE must maintain the index). Use them on columns you query frequently.
-- Basic index on a single column
CREATE INDEX idx_users_email ON users(email);
-- Unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_products_sku ON products(sku);
-- Composite index (on multiple columns)
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);
-- Partial index (index only a subset of rows)
CREATE INDEX idx_active_products ON products(category)
WHERE stock > 0;
Add indexes on columns that are:
WHERE clausesJOIN conditions (ON orders.user_id = users.id)ORDER BY on large tablesPrimary keys are automatically indexed. UNIQUE constraints create an index automatically.
EXPLAIN shows the query execution plan without running the query:
-- SQLite
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE user_id = 1;
-- PostgreSQL
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 1;
Without an index on user_id:
QUERY PLAN
Seq Scan on orders (cost=0.00..25.00 rows=10)
Filter: (user_id = 1)
(Sequential scan = reading every row)
After creating CREATE INDEX idx_orders_user_id ON orders(user_id):
QUERY PLAN
Index Scan using idx_orders_user_id on orders (cost=0.15..8.17 rows=2)
Index Cond: (user_id = 1)
(Index scan = jumping straight to matches)
DROP INDEX IF EXISTS idx_users_email;
-- ❌ Retrieves unnecessary columns
SELECT * FROM orders JOIN users ON orders.user_id = users.id;
-- ✅ Only fetch what you need
SELECT o.id, o.quantity, u.name FROM orders o JOIN users u ON o.user_id = u.id;
-- ❌ Gets all orders then filters in application code
SELECT * FROM orders;
-- ✅ Filter in the database
SELECT * FROM orders WHERE user_id = 1 AND order_date > '2024-01-01';
Subscribe to continue reading
Get full access to this lesson and all 10 lessons in this course.