SQL Performance Explained

A practical guide to SQL indexing and query optimization across all major database engines

Understand how indexes actually work so you can write SQL that runs fast, every time.

SQL Performance Explained cuts through the mystery of slow queries by focusing on the one mechanism that matters most: the index. Markus Winand walks you through how B-tree indexes work, how the database optimizer makes decisions, and how to write queries that take full advantage of your indexes. The book covers PostgreSQL, MySQL, Oracle, and SQL Server side by side, so the lessons apply wherever you work.

Buy on Amazon →

About this book

Most developers know that indexes make queries faster. Far fewer understand why — or why a query sometimes ignores an index entirely. That gap is where slow applications are born.

SQL Performance Explained closes that gap. Markus Winand starts from the physical structure of a B-tree index and builds up, step by step, to the execution decisions a database makes when it processes your query. Every concept is tied directly to a performance outcome you can measure and improve.

The book does not pick a side. PostgreSQL, MySQL, Oracle, and SQL Server all behave slightly differently, and Winand shows you exactly where they diverge. You finish the book able to reason about query performance on whichever system you are using today — and the next one.

Topics covered include:

How B-tree indexes are structured and traversed
Why column order inside a composite index changes everything
How the optimizer decides between an index scan and a full table scan
The performance cost of functions and implicit type conversions in WHERE clauses
Join strategies and how index design affects them
Partial indexes, covering indexes, and when to reach for each
How to read and interpret execution plans across all four major databases

At 196 pages, the book is short by design. Every page earns its place. You will not wade through database administration theory or installation guides. The focus stays on one question throughout: why is this query slow, and what index change will fix it?

Whether you write application SQL every day or tune queries for a living, this book gives you the mental model that makes index design feel obvious rather than trial-and-error.

🎯 What you'll learn

Explain the internal structure of a B-tree index and predict how a database will traverse it
Design composite indexes with correct column ordering for your specific query patterns
Identify why a query is skipping an index and fix the root cause
Read execution plans in PostgreSQL, MySQL, Oracle, and SQL Server
Avoid common WHERE clause patterns that silently destroy index performance
Choose between partial indexes, covering indexes, and full-column indexes based on real trade-offs
Optimize join queries by aligning index design with join strategy

👤 Who is this book for?

Backend developers who write SQL regularly but cannot reliably predict whether a query will be fast or slow
Application engineers who have hit a performance wall and suspect the database is the bottleneck
Database administrators who want a concise reference that covers indexing behavior across multiple engines
Full-stack developers moving from ORM-only work to writing and optimizing raw SQL
Engineering leads reviewing schema designs and query patterns in code review

01

Anatomy of an Index

Learn how a B-tree index is physically structured on disk and how a database traverses it to locate rows. This chapter builds the foundational mental model the rest of the book depends on.
02

The Where Clause

Explore how the database optimizer processes WHERE conditions and decides when to use an index. You will see exactly which query patterns are index-friendly and which ones force a full table scan.
03

Performance and Scalability

Understand why a query that runs fine on a small table can collapse under production data volumes. This chapter connects index behavior to the O(log n) cost model developers need to reason about scale.
04

The Join Operation

See how join strategies — nested loops, hash joins, merge joins — interact with index design. You will learn which indexes support which join types and how to design schemas that keep joins fast.
05

Clustering Data

Discover how the physical order of rows on disk affects query performance and when clustering or index-organized tables give you a significant speed advantage.
06

Sorting and Grouping

Learn why ORDER BY and GROUP BY can either exploit an existing index or trigger an expensive sort operation, and how to design indexes that eliminate unnecessary sorting.
07

Partial Results

Work through the performance implications of FETCH FIRST, TOP, and ROWNUM patterns, including how pipelined execution lets the database return the first row before reading the full result set.
08

Modifying Data

Examine the write-side cost of indexes: how INSERT, UPDATE, and DELETE performance degrades as index count grows, and how to balance read performance against write overhead.
09

Execution Plans

Read and interpret execution plans from PostgreSQL, MySQL, Oracle, and SQL Server side by side. You will finish this chapter able to diagnose a slow query on any of the four major engines.

Frequently asked questions

Do I need to be a database administrator to get value from this book?

No. The book is written for developers who write SQL as part of building applications. No database administration background is assumed or required.

Which database systems does the book cover?

The book covers PostgreSQL, MySQL, Oracle, and SQL Server throughout, noting where each engine behaves differently. Most indexing concepts apply equally across all four.

How much SQL do I need to know before reading this?

You should be comfortable writing SELECT statements with WHERE clauses, JOINs, and ORDER BY. The book does not teach SQL syntax — it explains why certain SQL runs slowly and how to fix it.

The book was published in 2012. Is the content still accurate?

The core indexing mechanics explained in the book — B-tree structure, optimizer behavior, execution plans — have not fundamentally changed in any of the covered engines. The principles remain accurate and applicable.

Is this a long book?

No. The book is 196 pages and is intentionally concise. Every chapter focuses on a specific performance concern, with no filler content.

Does the book include code examples or sample queries?

Yes. Every concept is illustrated with concrete SQL examples. The book uses consistent example schemas throughout so the code examples build on each other rather than starting fresh each chapter.