Indexes & Performance

Speed up queries dramatically with the right indexes — and know when not to use them

Knowledge0%

Learn & Drill

Fluency0%

Drill & Speed

Retention0%

Mastery & Review

Confidence0%

All modes

Practice

Free tier: read the explanation here. Upgrade to Pro for Drills, Speed challenges & Mastery badges.

Upgrade

Knowledge

Fluency

Retention

Knowledge Debt detected

You can study this freely — but your score may plateau if these foundations have gaps. The Mastery badge requires them to be solid.

GROUP BY & HAVING0%

Explanation

An index is a data structure (usually a B-tree) that lets the database find rows without scanning the entire table.

Without an index: Full table scan — checks every row. O(n). With an index: Tree lookup — logarithmic search. O(log n).

sql

-- Create an index
CREATE INDEX idx_users_email ON users(email);

-- Compound index (multiple columns)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Drop an index
DROP INDEX idx_users_email;

When to add an index:

Columns frequently used in WHERE, JOIN ON, ORDER BY
Foreign keys (almost always)
Columns with high cardinality (many distinct values)

When NOT to add an index:

Small tables (full scan is faster)
Columns with low cardinality (e.g. boolean — only 2 values)
Tables with many INSERT/UPDATE/DELETE (indexes slow down writes)

EXPLAIN / EXPLAIN ANALYZE — see how the database executes a query: ``sql EXPLAIN SELECT * FROM orders WHERE user_id = 42;

Examples

When an index helps vs hurts

Index design matches the query patterns in your application

-- HELPS: searching a large table by email (high cardinality)
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = 'alice@example.com';  -- fast!

-- HURTS performance: low cardinality column
-- Don't index: SELECT * FROM orders WHERE status = 'pending';
-- status has only 3-4 values — index is rarely selective enough

-- COMPOUND INDEX for a common query pattern:
CREATE INDEX idx_orders_lookup ON orders(user_id, status);
-- Speeds up: WHERE user_id = 5 AND status = 'shipped'

How well did you understand this?

Next in SQL & Databases

Python + SQL (psycopg2)

Continue