Database Index Design Patterns: Composite, Covering, and Partial Indexes

-- Query: WHERE status = 'active' AND created_at > '2025-01-01' ORDER BY created_at DESC

-- Good: equality (status) first, then range/sort (created_at)
CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);

-- Bad: range column first — wastes the index for the equality filter
CREATE INDEX idx_orders_bad ON orders (created_at, status);

-- Include all columns needed by the query to avoid heap lookups
-- Query: SELECT user_id, total FROM orders WHERE status = 'pending'

-- PostgreSQL: use INCLUDE for non-searchable columns
CREATE INDEX idx_orders_status_cover ON orders (status) INCLUDE (user_id, total);

-- MySQL: add to the index key itself
CREATE INDEX idx_orders_status_cover ON orders (status, user_id, total);

-- PostgreSQL: index only active orders (much smaller, faster)
CREATE INDEX idx_orders_active ON orders (created_at)
  WHERE status = 'active';

-- Only 2% of orders may be active — index is 50x smaller than full index

-- PostgreSQL: case-insensitive email lookups
CREATE INDEX idx_users_email_lower ON users (lower(email));
SELECT * FROM users WHERE lower(email) = lower('User@Example.com');

-- MySQL: expression index (MySQL 8.0+)
CREATE INDEX idx_users_email_lower ON users ((lower(email)));

-- PostgreSQL: tables with sequential scans
SELECT schemaname, relname, seq_scan, idx_scan,
       seq_scan - idx_scan AS missing_idx_approx
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan AND n_live_tup > 10000
ORDER BY seq_scan DESC
LIMIT 10;

-- MySQL: check slow query log for full table scans
-- Or use pt-query-digest to find top offenders

-- PostgreSQL: indexes with zero scans since last stats reset
SELECT schemaname, relname, indexrelname,
       idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;