A few weeks ago, we massively improved the performance of the Oh Dear dashboard and website by optimising our SQL queries. We documented everything we learned along the way in a 3-part series - from finding problematic queries, to fixing them with indexes, to preventing regressions with automated testing.
Here’s what each part covers:
Part 1: Finding the right queries to fix #
Before you can optimise anything, you need to know what to optimise. This post covers the tools and techniques we use to identify slow queries: Laravel’s debug bar for local development, MySQL’s slow query log, logging queries that don’t use indexes, inspecting currently running queries, and using pt-query-digest to analyse query patterns. We also cover Larav…
A few weeks ago, we massively improved the performance of the Oh Dear dashboard and website by optimising our SQL queries. We documented everything we learned along the way in a 3-part series - from finding problematic queries, to fixing them with indexes, to preventing regressions with automated testing.
Here’s what each part covers:
Part 1: Finding the right queries to fix #
Before you can optimise anything, you need to know what to optimise. This post covers the tools and techniques we use to identify slow queries: Laravel’s debug bar for local development, MySQL’s slow query log, logging queries that don’t use indexes, inspecting currently running queries, and using pt-query-digest to analyse query patterns. We also cover Laravel’s built-in lazy loading prevention for catching N+1 issues early.
Read part 1: Finding the right queries to fix →
Part 2: Analysing & fixing the slow queries #
Once you’ve found the problematic queries, how do you actually fix them? This post dives deep into MySQL indexes: how EXPLAIN works, what each output field means, how to read query execution plans, and how to design effective composite indexes. We cover the difference between full table scans, index scans, and index seeks - and show real examples of queries going from seconds to milliseconds with the right index.
Read part 2: Analysing & fixing the slow queries →
Part 3: Automatic detection & regression testing #
Fixing performance issues is one thing - keeping them fixed is another. This post introduces our open-source package phpunit-query-count-assertions that lets you assert SQL performance in your test suite. Catch N+1 queries, duplicate queries, and missing indexes automatically in CI before they reach production. We’ve eliminated over 15% of our SQL queries by catching duplicates that slipped through code review.
Read part 3: Automatic detection & regression testing →
Whether you’re just starting to think about database performance or looking to prevent regressions in an already-optimised application, there’s something in this series for you.