Transactions, Performance, and Production SQLLesson 6.4

Common query rewrites that make SQL faster

SELECT star vs specific columns, DISTINCT vs GROUP BY, subquery vs JOIN performance, function on indexed column, partial indexes, covering indexes

Small Changes With Big Impact

Most slow queries have a simple fix once you know the patterns.

Avoid Functions on Indexed Columns in WHERE

-- Slow: function prevents index use
SELECT * FROM orders
WHERE DATE_TRUNC('month', created_at) = '2024-01-01';

-- Fast: range scan uses index on created_at
SELECT * FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at < '2024-02-01';

Partial Indexes for Subset Queries

-- Index only the rows you actually query
CREATE INDEX idx_orders_pending
  ON orders(created_at)
  WHERE status = 'pending';
-- Much smaller index, much faster for pending-order queries

Covering Indexes

A covering index includes all columns the query needs — the database never touches the table:

CREATE INDEX idx_orders_covering
  ON orders(customer_id, created_at, amount);
-- Query asking for amount WHERE customer_id = X ORDER BY created_at
-- is answered entirely from the index

DISTINCT vs GROUP BY

For deduplication without aggregation, DISTINCT and GROUP BY produce the same result. GROUP BY is often faster when you're already grouping; DISTINCT is clearer for simple deduplication.