The Missing Book
To Understand
Indexes
Learn to create indexes for your slow queries by studying complicated database internals following simple principles.
Indexing Beyond The Basics Book
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
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.
Tobias Petry
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.
Gotchas & 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
The Interaction of Indexes and Tables
10
2. Index Access Principles
Principle 1: Fast Lookup
14
Principle 2: Scan in One Direction Preview
15
Principle 3: From Left To Right
16
Principle 4: Scan On Range Conditions
22
3. Index Supported Operations
Inequality (!=)
25
Nullable Values (IS NULL and IS NOT NULL)
28
Pattern Matching (LIKE)
30
Sorting Values (ORDER BY)
31
Aggregating Values (DISTINCT and GROUP BY)
33
Joins
39
Subqueries
43
Data Manipulation (UPDATE and DELETE)
47
4. Why Isn’t the Database Using My Index?
The Index Can’t Be Used
49
No Index Will Be the Fastest
52
Another index is faster
56
5. Pitfalls and Tips
Indexes on Functions
60
Boolean Flags
61
Transforming Range Conditions
63
Leading Wildcard Search
67
Type Juggling
69
Index-Only Queries
70
Filtering and Sorting With Joins
72
Exceeding the Maximum Index Size
74
JSON Objects and Arrays
77
Unique Indexes and Null
80
Location-Based Searching With Bounding-Boxes
82
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
The Complete Experience
$199
$149
USD
plus local taxes
The full book as PDF
The full book as EPUB for e-book readers
The full book online
All Videos
Lifetime Updates
$50 off for the first 900 customers (5 left)
Supported Payments
Frequently Asked Questions
01
Why a book and not a video course?
  • More information: Video courses never go in-depth as far as a book because video production takes so much time that the range of topics has to be strictly limited.
  • Faster to learn: Watching all chapters of a video course takes a long time. I focused on writing a book that is quick to read with many visual examples.
  • Quick reference: With a book, you can efficiently re-read a single chapter or paragraph. Reading up on some stuff later again is easy.
02
For which databases is the book?
This book is designed to teach knowledge about indexes independently of any database brand. Only a few pages describe unique behaviors of MySQL or PostgreSQL, which are discussed in more detail.
03
Can I get an invoice?
You get a confirmation from Lemon Squeezy that you can generate an invoice after buying. You only have to fill in your billing information to download the invoice instantly.
04
Can I upgrade later?
Sure! Please send me an email to hello@sqlfordevs.com to discuss it.
05
What if I don't like it?
I suspect you will love the book, but I also have no problem if you don't. Just email me at hello@sqlfordevs.com within 30 days of your purchase. I'll refund you in full, no questions asked.
06
I have another question
Send me an email at hello@sqlfordevs.com. I am happy to hear from you :)