MySQL Performance Backend

The call came at 11AM on a Tuesday. A client's SaaS dashboard was timing out for users with large accounts. The error: "504 Gateway Timeout." The cause: a single MySQL query scanning 3.1 million rows on every dashboard load.

Here is the full story of how we found it and fixed it.

Step 1: Identify the Offender

The first tool I reach for in database performance issues is MySQL's slow query log. If you do not have it enabled in production, enable it now:

Within 20 minutes of enabling this, the log had captured our culprit. The query appeared 847 times in a 20-minute window. Average execution time: 8.3 seconds.

Step 2: Run EXPLAIN

The original query looked like this:

Running EXPLAIN on this query revealed the problem immediately:

table type possible_keys rows Extra
projects (p) ALL NULL 3,142,847 Using filesort
clients (c) eq_ref PRIMARY 1
users (u) eq_ref PRIMARY 1
project_tasks (pt) ALL NULL 8,400,000 Using temporary

Two full table scans. A filesort on 3 million rows. A temporary table with 8 million rows. This query was doing the absolute worst possible things.

Step 3: The Fix

The solution had three parts:

01
Add composite indexes that match the query's WHERE clause and ORDER BY. The order of columns in the index matters — most selective column first.
02
Rewrite the subquery to avoid the FILTER clause (MySQL compatibility) and use a more index-friendly COUNT pattern.
03
Add a covering index so MySQL can answer the query entirely from the index without touching the table data.

After adding the indexes, EXPLAIN showed:

table type key rows Extra
projects (p) range idx_company_status_updated 312 Using index
project_tasks (pt) ref idx_project_status 4 Using index

From 3.1 million rows examined to 312. From 8.3 seconds to 40 milliseconds. A 99.5% reduction in query time from two ALTER TABLE statements.

🎯
Key principle: If your EXPLAIN shows type: ALL on any table with more than 10,000 rows, you have a problem. Fix it before it finds you at 11AM on a Tuesday.

Is your database slowing you down?

Database performance problems compound over time. What is a 300ms query today is an 8-second timeout when your data doubles. We audit and fix database performance issues before they become incidents.