What I Learned About Indexes, Query Plans, and Real Database Performance

Recently I was in an interview where I had to write SQL on a simple set of tables, without any parsing or assistance. Just a CTO looking at me and my screen. I felt like a complete idiot, not able complete the query without their interjection, somehow they came back with an offer after some time.

That feeling is not something I want to have again, so I started this series of deep dives.

Diving in the deep — tech talk
Hi, my name is Gary, I’ve worked with Javascript and Typescript across Vue, Nuxt, React, React Native and Next for over…medium.com

In my day to day, using Laravel, I am writing queries all the time, but since I work through Eloquent rather than writing SQL directly, I wanted to revisit what the database is doing underneath the abstraction.

Although Laravel is an amazing language, written by some great minds, it’s likely that it has not helped in making my querying and ability to define indices any better.

The first resource I used was a refresher on basic SQL queries and SQLBolt’s interactive tutorial was great. It gives an understanding of the basics, moving on to JOIN logic, aggregates and modifying rows and tables.

SQLBolt - Learn SQL - Introduction to SQL
SQLBolt provides a set of interactive lessons and exercises to help you learn SQLsqlbolt.com

Next I moved on topics that I don’t necessarily touch upon that often. I spent time learning how to evaluate whether an index is actually helping or hurting performance.

The starting point is EXPLAIN, which shows how MySQL or PostgreSQL plans to execute a query: whether it uses an index, which index it chooses, how many rows it expects to examine, and whether it needs operations such as a table scan or filesort. For more detailed validation, EXPLAIN ANALYZE executes the query and reports actual timing and row counts, allowing you to compare the optimizer’s estimates with what really happened. You’ll see it work best with large datasets, so you should find a way to test it out with large sets, larger than your production expectation.

I also looked at MySQL’s Performance Schema, particularly performance_schema.table_io_waits_summary_by_index_usage. This is the closest equivalent to PostgreSQL’s pg_stat_user_indexes. It provides counters showing how often indexes have been involved in reads and writes, which can reveal indexes that are heavily used as well as indexes that are never read but still add maintenance overhead to inserts and modifications. However, these statistics need to be interpreted carefully: a rarely used index may still support an occasional important query, and the counters only represent the period for which statistics have been collected.

The main lesson

An index should not be judged simply by asking, “Does this table have an index?” The better question is: Does this index make important queries faster without creating too much extra cost elsewhere?

To answer that, we need to combine several sources of evidence:

• The query plan: what SQL intends to do.
• Actual execution time: how long it really took.
• Row estimates: how much data SQL expected to inspect.
• I/O activity: how much data had to be read.
• Index usage: whether the index is used in practice.
• Read/write workload: whether the index helps enough reads to justify its maintenance cost.

You can checkout some practical examples of this on a small repository I’ve set up that you can run locally through docker.

GitHub - garyThrels/sql-explain
Contribute to garyThrels/sql-explain development by creating an account on GitHub.github.com

The main lesson was that indexes should not be evaluated in isolation. I need to combine query plans, actual execution times, row estimates, I/O activity, index usage, and the table’s read/write workload. An index is valuable when it consistently improves important queries by reducing the amount of data examined; it may be harmful when it is unused, poorly selective, bloated, or adding significant write and storage overhead without providing a measurable benefit.

I intended this to be a quick recap on SQL, and an easy way to examine how EXPLAIN and indexing can work. It ended up taking several weeks of tweaking, adjustments and juggling to find the time. However I hope this article can actually help someone in their understanding of the SQL.