Database Indexing Explained: How to Make Your Queries 100x Faster

                    [50]
                   /    \
              [25]        [75]
             /    \      /    \
          [10,20] [30,40] [60,70] [80,90]
              ↓      ↓       ↓       ↓
           [rows]  [rows]  [rows]  [rows]

sql
-- Single column index
CREATE INDEX idx_users_email ON users(email);

-- Composite index (order matters!)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Partial index (only index a subset of rows)
CREATE INDEX idx_orders_pending ON orders(status)
WHERE status = 'pending';

✅ Columns used in WHERE clauses
✅ Columns used in JOIN conditions
✅ Columns used in ORDER BY
✅ Foreign key columns
✅ Columns with high cardinality (many unique values)

❌ Small tables (< 1000 rows)
❌ Columns that are rarely queried
❌ Columns with low cardinality (boolean, status)
❌ Tables with heavy write operations

sql
-- Index: (user_id, created_at)

-- ✅ Uses the index (left-most prefix)
SELECT * FROM orders WHERE user_id = 123;
SELECT * FROM orders WHERE user_id = 123 AND created_at > '2024-01-01';

-- ❌ Does NOT use the index efficiently
SELECT * FROM orders WHERE created_at > '2024-01-01';

sql
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john@example.com';

-- Good: "Index Scan using idx_users_email"
-- Bad:  "Seq Scan on users" (full table scan)