A complete book dedicated to database indexes

The Missing Book

To Understand

Indexes

Learn to create indexes for your slow queries by studying complicated database internals following simple principles.

Buy Now

Get Two Free Chapters

I never felt confident using indexes. They had been a thing of mystery to me before reading the book.

Caleb Porzio

Creator of Livewire & Alpine.js

The helpful illustrations make it my new standard recommendation to dive deep into indexes.

Aaron Francis

Educator at PlanetScale

I added indexes randomly until the query was fast or I gave up.

Hi! I'm Tobias Petry, a full stack developer who used to suck at writing fast database queries. Whenever I had a slow query, I needed to ask more advanced developers for help and hope they had time for me.

Like everyone, I always wished I could solve those problems on my own. But even after reading some tutorials I didn't get any closer to my goal. The number of queries I couldn't optimize by far exceeded the ones I could make faster.

So I learned everything about databases over the next 10+ years from countless books, conferences, trainings, articles and much practice. Finally, I could fix any slow query entirely without help. But during my consulting work I found that most developers still face the same problem.

Author

Tobias Petry

The problem is the available content

The reason database indexes are still not understood by developers is the depth of the existing content. Whether it is books, articles or videos - they all have their own problems that limit the ability to understand database indexing.

Blog articles and Youtube videos explain indexes by short examples on the most trivial problems (e.g. a missing single-column index) and won't go in-depth any further. But the minimal knowledge learned from these examples cannot be applied to your more complex queries.

However, published books are written for database administrators (DBA) and teach everything from backup approaches over complex database internals to obscure tuning settings that make it hard for developers to get actionable advice. Most likely, you have already stopped reading at least one of these books with 500+ pages because they include too many topics you are not interested in. In the end, database indexes were only covered on a few pages and are not sufficiently explained because the target audience of these books is interested in other topics.

Who is this book for?

You are a developer who can write SQL queries but struggle with creating the correct indexes to make them fast. Neither do you have the time or interest to learn this topic in weeks - a couple evenings on the couch should be enough.

You also just want to learn the practical knowledge necessary to complete your task and move on to other things. The content should avoid any technical complex database implementation details - you want the core information quickly, concisely and understandably.

This book strikes the perfect balance between theory and application.

Stef Rouschop

Lead Developer at 3rdRisk

The tricky subject of indexing has been turned into an easy thing for me.

Tim Geisendörfer

Founder at InnoGE GmbH

A clear and concise resource that helped me better understand indexes.

Michael Daly

CTO at SwingU

The Book

A picture is worth a thousand words.

Many indexing concepts require hundreds of words to explain them in detail. Following those walls of text and the author's ideas behind them is difficult sometimes. This book takes a different approach and explains key indexing behaviors with more than 60 illustrations to make them much easier to understand.

Chapters

Fundamentals & Indexing Access Principles

You learn the fundamentals of indexes without going into useless technical implementation details. Everything is explained with a novel approach to visualize indexes and four principles I invented to describe how they are used by queries. You can apply these rules whenever you need to create an index for a query and they will guide whether the index is a good fit or not - e.g. on the optimal order of columns in a multi-column index.

Index Supported Operations

You will improve your knowledge by applying the four indexing principles on all possible SQL operations (e.g. ORDER BY, joins, subqueries, etc.) - including the unique requirements of each one. You can consult this reference guide at any time later to optimize a query.

Why isn’t the database using my index?

Sometimes, another index is used than you expected - or none at all. This is incredibly annoying when you create a new index to make a query faster. So you learn how the database selects an index, why yours has probably been ignored and which problems you should be aware of to avoid frustration in the future.

Pitfalls & Tips

Knowing all the principles for creating fitting indexes is great. However, there are some exceptional cases you should be aware of. This section is a mix of common pitfalls you should know to avoid them and some tricks I gathered over many years.

Table Of Contents

1. Fundamentals

A Different View on B+ Trees

7

Single vs Multi-Column Indexes

10

The Interaction of Indexes and Tables

14

2. Index Access Principles

Principle 1: Fast Lookup

18

Principle 2: Scan in One Direction Free Chapter

19

Principle 3: From Left To Right

20

Principle 4: Scan On Range Conditions

26

3. Index Supported Operations

Inequality (!=)

29

Nullable Values (IS NULL and IS NOT NULL)

32

Pattern Matching (LIKE)

34

Sorting Values (ORDER BY)

35

Aggregating Values (DISTINCT and GROUP BY)

37

Joins

43

Subqueries

47

Data Manipulation (UPDATE and DELETE)

51

4. Why Isn’t the Database Using My Index?

The Index Can’t Be Used

53

No Index Will Be the Fastest

56

Another index is faster

60

5. Pitfalls and Tips

Indexes on Functions

64

Boolean Flags

65

Unique Indexes and Null

67

Partial Index

69

Transforming Range Conditions

72

Leading Wildcard Search

76

Type Juggling

78

Index-Only Queries

79

Filtering and Sorting With Joins

81

Exceeding the Maximum Index Size

83

JSON Objects and Arrays

86

Location-Based Searching With Bounding-Boxes

89

See All The Chapters

The Videos

The best of video courses

Books have much higher information density, are faster to finish from start to finish and you can quickly re-read parts weeks later. But for a few concepts, easy-to-follow step-by-step presentations are easier to understand.

Topics

41:56

1. Understanding Execution Plans

Reading and understanding execution plans is the most challenging topic of query tuning. Step-by-step, you learn how to read the EXPLAIN ANALYZE output, identify the slow parts of a query and which indexes should be added.

11:52

2. Joins Vs. Subqueries

Everyone still argues whether joins or subqueries are more efficient. Usually, subqueries are automatically rewritten into joins. Learn to identify when this is happening and when it's worth changing the query yourself.

3:40

3. The Impact Of Schema And Queries

Performance is influenced by queries and the schema of the tables. Learn what impact tables with many or big columns have and why SELECT * typically used by ORMs can have a significant performance impact.

Learn Correct Indexing Today

Slow queries are costing you hundreds of dollars in server bills each month.
Learn how to create optimal indexes by yourself to fix these bottlenecks.

Only The Essentials

$99

USD

plus local taxes

The full book as PDF

Lifetime Updates