Finding the query that is actually slow
This is a sample post so you can see how a longer article renders. Replace or delete it.
Most “the site is slow” reports turn out to be one or two queries, not the whole application. The trick is finding them without guessing.
1. Turn on the slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = 'ON';Half a second is a deliberately low threshold. You want the log noisy at first so you can see the shape of the problem.
2. Summarize, don’t read
Reading the raw log is a waste of time. Use mysqldumpslow or pt-query-digest to get
the queries ranked by total time spent, not by individual slowness:
pt-query-digest /var/log/mysql/slow.log | head -50A query taking 80ms but running 10,000 times per hour costs you far more than the one taking 3 seconds once a day. Total time is the number that matters.
3. Explain the worst offender
EXPLAIN SELECT ...;What you’re looking for, roughly in order of how much it hurts:
| Sign | What it means |
|---|---|
type: ALL |
Full table scan, no usable index |
Using filesort |
Sorting in memory or on disk after the fact |
Using temporary |
Building a temp table to satisfy the query |
High rows |
Examining far more rows than it returns |
4. Fix the cause, not the symptom
An index is the obvious fix and often the right one. But check first whether the query should be running at all — the fastest query is the one your app doesn’t make. N+1 patterns in an ORM cause more slow sites than missing indexes do.
5. Turn the log back down
long_query_time = 0.5 on a busy production box will fill a disk. Set it back to
something sane, or turn it off, once you’ve got what you need.