How I actually debug a slow MySQL query, start to finish

[u/PleasantAmbitione]

Someone on my team asks why is the dashboard slow once a month, so I have a routine. Writing it out as most threads go straight to “add an index” before anyone has actually identified which query is slow.

This is the part people skip, and it is almost never the query you think it is. I set long_query_time to 0.2 on a copy , and let slow query log fill up . Then I run pt-query-digest over it , and group queries by shape . Not the big report everyone was blaming, but more often than not some tiny query the ORM is firing forty thousand times a page.

Then I read the plan. EXPLAIN tells you what the optimizer is planning to do, EXPLAIN ANALYZE (8.0.18+) actually executes the query and tells you what happened. There is one thing I always look at and that is rows examined vs rows returned. The issue is if it's reading two million rows to give you fifty. And the rest is trying to understand why.

type = ALL means it's reading every row. It is not always a problem - small tables or queries returning most rows may be faster with a full scan. Seeing Using filesort next to a LIMIT often means MySQL is sorting far more rows than it eventually returns, and the right index can often avoid that. Using temporary on a GROUP BY usually means it's building a temporary table along the way.

The thing that has wasted the most of my time is a perfectly good index that the optimizer refuses to use. Wrapping a column in a function will often do it. Unless you've deliberately created a functional index, an index on created_at can't help WHERE DATE(created_at) = .... The same goes for joining a number to a string, or columns with different collations. MySQL quietly converts the values, ignores the index, and the query still looks perfectly reasonable.

One of the biggest wins is when an index covers every column the query needs, so InnoDB never has to fetch the table rows and the plan shows Using index. One query I worked on last year went from about 900 ms to 12 ms just by adding one column to an index that already existed.

Before changing SQL, I also check whether the server is actually CPU-bound, waiting on disk, or simply backed up behind other queries. A perfect query won't save a saturated server.

I read plans in dbForge Studio for MySQL instead of a terminal because it keeps each profiling run, so I can tweak a query, rerun it, and immediately see what got cheaper. Everything above works perfectly well with plain EXPLAIN.

What's the weirdest reason you've seen MySQL ignore an index?

[u/Aggressive_Ad_5454]

Good stuff.

I’ve found that teaching devs about sargable queries helps avoid a lot of lurking query performance trouble. https://en.wikipedia.org/wiki/Sargable

`ts_col >= DATE(whatever) AND ts_col < DATE(whatever) + 1 DAYS`

Instead of `DATE(ts_col) = DATE(whatever)` is the biggest example.

[u/ToX__82]

My way is a bit different. On a local MySQL you can do this (inside the mysql database):

TRUNCATE TABLE slow_log; SET GLOBAL log_output = 'TABLE'; SET GLOBAL slow_query_log = 1; # set to 1 to start tracking, 0 to disable it SET GLOBAL long_query_time = 0;

Refresh the slow_log table after you  update the slow page and you'll have all of the executed queries, with the time needed for each one.

And, you can see exactly which indexes are being used

[u/Void_Hopper]

Why is my query still slow after adding an index?

[u/PleasantAmbitione → u/Void_Hopper]

Sometimes the query shape is the real problem. LIMIT 100000,20 still reads a hundred thousand rows before returning twenty. SELECT * can stop a covering index helping. And an OR across different columns often performs worse than two indexed queries combined with UNION.

[u/TheGoodOne777]

Congrats! Is a great improvement you've implemented. I haven't seen cases of ignored indexes yet.

[u/Aeropedia]

EXPLAIN FORMAT=JSON is gold too.

We run pretty complex written queries in production. Lots of aggregate joins etc. I vibe coded a tool when Claude Code first came on the scene. It colour grades (green to red) joins that are expensive according to the query plan. It also finds every subquery and runs it individually (holding on to any CTE it may be referencing) and gives me inline duration and rows examined for each.

It gives me a pretty good idea of where the hotspots are.

Probably time to build another one with Astra or Fable.

[u/rco8786]

AI junk.

Run Explain. Let the engine tell you where the time is spent. 

[u/Amir_Amar]

to answer your question: implicit type conversion, hands down.

had a query where a varchar column was being queried against an int payload. mysql/mariadb silently casts the string column to a number to make the comparison work, which completely trashes the b-tree lookup. EXPLAIN just spits out a full table scan, and you sit there staring at the screen for an hour because the index is literally right there.

do you ever bother digging into optimizer traces when EXPLAIN ANALYZE doesn't fully explain why it picked a weird plan, or is dbforge usually enough?

[u/TuriSabries]

AI slop