SQL Query Optimization Basics

This module covered what an index is, how to index more than one column, and how to see whether a query actually used one. This last lesson ties all three together into the habits that keep a query fast as Beacon Robotics' tables keep growing.

Learning Objectives

• List the columns worth indexing for a given query: the ones in its WHERE, JOIN, and ORDER BY clauses.
• Explain sargability, and why wrapping an indexed column in a function usually defeats the index on it.
• Explain why a large OFFSET value gets slower as it grows, and what keyset pagination does differently.

Index the Columns a Query Actually Uses

The two previous lessons already covered this in pieces, bring it together here: a column is worth indexing if a real query filters on it (WHERE), joins on it (ON), or sorts by it (ORDER BY). A column that's only ever selected, never filtered, joined, or sorted on, gets no benefit from an index, it's pure write overhead with nothing to show for it.

Sargability: Keep the Indexed Column Naked

A "sargable" predicate is one the optimizer can resolve directly against an index. Wrapping an indexed column in a function, like YEAR(hire_date) = 2024 or UPPER(last_name) = 'SMITH', usually forces the engine to compute that function for every single row before it can compare anything, which turns what should be an index seek back into a full scan.

SELECT * FROM employees
WHERE YEAR(hire_date) = 2024;

SELECT * FROM employees
WHERE hire_date >= '2024-01-01'
  AND hire_date < '2025-01-01';

When the Function Form Is Unavoidable: Expression Indexes

SELECT * and Index-Only Access

A query that only needs columns already present in an index can sometimes be answered entirely from the index itself, without a separate trip back to the full row at all, PostgreSQL calls this an Index Only Scan, the general term used across MySQL and SQL Server is a covering index. SELECT * defeats this every time, since it forces a return to the full row for every column that isn't part of the index.

Pagination: Large OFFSET vs Keyset Pagination

Module 1 covered LIMIT/OFFSET's syntax differences, here's the performance angle: a large OFFSET still has to walk through and discard every skipped row first, even with an index on the ORDER BY column, so OFFSET 100000 LIMIT 20 gets slower as that offset number grows. Keyset pagination, WHERE id > :last_seen_id ORDER BY id LIMIT 20, stays fast at any depth, since it's a direct indexed seek to a known starting point with nothing discarded along the way.

SELECT * FROM employees
ORDER BY id
LIMIT 20 OFFSET 100000;

SELECT * FROM employees
WHERE id > 100000
ORDER BY id
LIMIT 20;

Try It Yourself

Rewrite this non-sargable query so the WHERE clause itself compares the raw hire_date column, on PostgreSQL: SELECT * FROM employees WHERE YEAR(hire_date) = 2023;

SELECT * FROM employees
WHERE hire_date >= '2023-01-01'
  AND hire_date < '2024-01-01';

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• Index WHERE, JOIN, and ORDER BY columns, specifically the ones a real query uses, confirmed with EXPLAIN rather than assumed.
• Avoid wrapping an indexed column in a function inside WHERE, rewrite the comparison instead, or build a matching expression index when the function form can't be avoided.
• Prefer keyset pagination over a large OFFSET for deep pages on a table that keeps growing.

Interview Questions

Summary

Index the columns a real query filters, joins, or sorts by, keep predicates sargable so an existing index actually gets used, and prefer keyset pagination over a large OFFSET once a table is big enough for the difference to matter. None of this replaces EXPLAIN, confirm it on the real data rather than assuming it.

That completes Module 13, Indexes and Performance: what an index actually is, indexing more than one column at once, reading a real execution plan, and the query habits that keep an index worth having.

What's Next?

Module 14, Transactions and ACID, is next: grouping multiple statements into one all-or-nothing unit, and what COMMIT, ROLLBACK, and the ACID guarantees actually mean underneath.