Skip to content
Tutorials Center
My learning

Topics

Lesson 48 of 75 ยท Indexes and Performance

Finding Slow Queries

Find the queries that hurt: the slow query log, long_query_time, pt-query-digest, performance_schema digests, the sys schema and handler counters.

You cannot tune what you cannot see. A database with a thousand different queries usually has a handful that cause nearly all the load, and the trick is to find those few instead of guessing. MySQL gives you two complementary sources: the slow query log, which records individual statements that took too long, and performance_schema, which keeps running statistics for every kind of statement since the server started.

This lesson shows how to switch both on, how to read them, and how to turn raw numbers into a ranked list of what to fix first. The server features (the log file, performance_schema, the sys schema, status counters) need a real MySQL server, so those blocks are read-only. The two live examples use the SQLite stand-in to practice the part that is plain SQL: summarizing a log of queries and spotting the ones that examine far more rows than they return.

Two ways to catch slow SQL

Slow query log One entry per slow statement, with timings.
performance_schema Running totals per query shape, always on.

Use the log for detail and the digests for ranking.

1 / 2

The slow query log

The slow query log is off by default. You turn it on with a few variables, all changeable at run time:

SQL
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;            -- seconds; fractions are allowed
SET GLOBAL log_queries_not_using_indexes = OFF;
SHOW VARIABLES LIKE 'slow_query_log_file';   -- where the file lives

These statements are read-only here because they configure a real server.

  • long_query_time is the threshold in seconds. The default is 10, which is far too high for a web application. Values between 0.1 and 1 are typical. Setting it to 0 logs every statement, which is useful for a short sampling window and dangerous to leave on.
  • min_examined_row_limit skips statements that examined fewer rows than the limit, which filters out trivial queries.
  • log_queries_not_using_indexes also logs statements that did not use an index, even when they were fast. On a system with many small tables this is noisy, so most teams leave it off.
  • log_output chooses FILE (the default), TABLE (the mysql.slow_log table) or both. The file is faster and easier for tools to read.
  • log_slow_extra (MySQL 8.0.14 and later) adds extra fields such as temporary tables created and bytes sent.

A change with SET GLOBAL long_query_time affects new connections only. Existing connections keep the session value they started with, so a connection pool may keep logging at the old threshold until its connections are replaced. To keep the setting across a restart, put it in my.cnf or use SET PERSIST (see the server tuning lesson).

An entry in the file looks like this:

TEXT
# Time: 2026-03-14T10:22:07.114523Z
# User@Host: app[app] @ 10.0.2.15 []  Id: 4821
# Query_time: 3.482901  Lock_time: 0.000112  Rows_sent: 20  Rows_examined: 2481520
SET timestamp=1773483727;
SELECT * FROM orders WHERE customer_email = '[email protected]' ORDER BY created_at DESC LIMIT 20;

Read the numbers as a story. This query took 3.5 seconds, returned 20 rows, but examined about 2.5 million. Ratios like that almost always mean there is no useful index on customer_email. Lock_time shows time spent waiting for locks, so a large value there points at contention rather than a bad plan.

Summarizing the log: pt-query-digest

A busy server writes thousands of entries per hour, and reading them by hand is hopeless. The same query appears with different literal values ('ada@...', 'bo@...'), so you must group entries by query shape, replacing literals with placeholders. The standard tool is pt-query-digest from Percona Toolkit:

TEXT
$ pt-query-digest /var/lib/mysql/db1-slow.log | head -40

# Profile
# Rank Query ID           Response time   Calls  R/Call V/M   Item
# ==== ================== =============== ====== ====== ===== ============
#    1 0xA1B2C3D4E5F60718 1840.2210 62.3%    512 3.5942  0.41 SELECT orders
#    2 0x1122334455667788  610.9004 20.7%  48200 0.0127  0.02 SELECT products

It ranks shapes by total response time, which is the number that matters. Query 1 is slow per call. Query 2 is fast per call but runs 48,200 times and uses 20% of the time. Both deserve attention, and only a summary like this shows the second one.

You can practice the idea with plain SQL. This example builds a small log table and groups it by shape, the way the digest tools do:

SQL Rank query shapes by total time

Press Run (or Ctrl + Enter) to see the result here.

The orders shape comes first with 10.5 seconds in total from three calls. Notice the products lookup: each call took well under a second, yet it adds up to 3.9 seconds in total. Sorting by the single slowest call would have hidden it.

performance_schema digests

The slow log only records statements over the threshold. performance_schema, which is on by default in MySQL 8.0, records every statement into a summary table, grouped by a normalized text called a digest:

SQL
SELECT DIGEST_TEXT,
       COUNT_STAR                          AS calls,
       ROUND(SUM_TIMER_WAIT / 1e12, 2)     AS total_seconds,
       ROUND(AVG_TIMER_WAIT / 1e9, 2)      AS avg_ms,
       SUM_ROWS_EXAMINED                   AS rows_examined,
       SUM_ROWS_SENT                       AS rows_sent,
       SUM_NO_INDEX_USED                   AS no_index_used
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Timer columns are in picoseconds, so divide by 1e12 for seconds and 1e9 for milliseconds. The digest text replaces literals with ?, so WHERE id = 7 and WHERE id = 9 land in one row. The counters start at zero when the server starts, and TRUNCATE TABLE performance_schema.events_statements_summary_by_digest; resets them, which is handy before a load test.

Because that query is long to type, MySQL ships the sys schema, a set of readable views over performance_schema:

SQL
SELECT * FROM sys.statement_analysis LIMIT 5;           -- slowest shapes, formatted
SELECT * FROM sys.statements_with_full_table_scans;     -- scans without an index
SELECT * FROM sys.statements_with_temp_tables;          -- tmp tables, esp. on disk
SELECT * FROM sys.statements_with_sorting;              -- filesort heavy statements

sys.statement_analysis returns human-friendly units like 3.48 s. For scripts, use sys.x$statement_analysis, which holds the same data as raw numbers. The data dictionary lesson says more about sys.

The digest tables show which shapes are costly but not which parameter values or when. Use them to find candidates, then reproduce a candidate with EXPLAIN or EXPLAIN ANALYZE (see EXPLAIN).

Rows examined versus rows sent

The most useful single ratio is rows examined divided by rows sent. A query that returns 20 rows after reading 20 is efficient. One that reads 2.5 million to return 20 is wasting work, however fast the hardware makes it feel today. The same ratio works on your own data:

SQL Spot the scan-heavy queries

Press Run (or Ctrl + Enter) to see the result here.

The orders lookup examines 124,000 times more rows than it returns, which is a textbook missing index. The products lookup has a perfect 1.0 ratio, so it is efficient even though it ran 48,200 times.

Handler counters

When you are examining one statement, session status counters tell you how the engine read rows. Reset them, run the query, then look:

SQL
FLUSH STATUS;
SELECT * FROM orders WHERE customer_email = '[email protected]';
SHOW SESSION STATUS LIKE 'Handler_read%';
TEXT
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Handler_read_first    | 1       |
| Handler_read_key      | 1       |
| Handler_read_last     | 0       |
| Handler_read_next     | 0       |
| Handler_read_prev     | 0       |
| Handler_read_rnd      | 0       |
| Handler_read_rnd_next | 2481521 |
+-----------------------+---------+

A huge Handler_read_rnd_next means the engine walked the table row by row (a full scan). A high Handler_read_key with a small Handler_read_next means it jumped straight into an index. Handler_read_rnd counts reads by position, which usually follows a sort. The values above are illustrative, so your numbers will differ.

SHOW PROFILE also exists, but it has been deprecated for years. Prefer performance_schema (statement and stage events) and EXPLAIN ANALYZE on current versions.

A routine that works

  1. Set long_query_time to 0.5 or so, or sample at 0 for ten minutes during busy hours.
  2. Rank by total time (digest tools or sys.statement_analysis), not by the single slowest query.
  3. For the top three, check rows examined against rows sent, then run EXPLAIN.
  4. Fix one thing (usually an index or a rewrite), measure again, and repeat.
  5. Keep the log on at a moderate threshold, and rotate the file so it does not fill the disk.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which ranking helps you find the queries that cost the most overall?

    A fast query that runs very often can use more total time than one slow query that runs rarely, so rank by total time.
  2. 2. You run SET GLOBAL long_query_time = 0.5. Which connections use the new value?

    The global value is copied into each new session. Connections that already exist keep the value they started with.
  3. 3. In performance_schema, what unit do the timer columns such as SUM_TIMER_WAIT use?

    Timers are in picoseconds, so divide by 1e12 to get seconds.
  4. 4. A slow log entry shows Rows_sent: 20 and Rows_examined: 2480000. What does that most likely mean?

    A huge gap between rows examined and rows sent points at a scan caused by a missing or unusable index. Lock waits show up in Lock_time instead.

Summary

  • The slow query log records statements over long_query_time. The default of 10 seconds is too high, so use roughly 0.1 to 1.
  • Group entries by query shape with pt-query-digest, and rank by total response time.
  • performance_schema.events_statements_summary_by_digest keeps running totals for every statement shape, in picoseconds. The sys views (statement_analysis, statements_with_full_table_scans) make them readable.
  • Rows examined far above rows sent is the clearest sign of a missing index.
  • FLUSH STATUS plus SHOW SESSION STATUS LIKE 'Handler_read%' shows whether one statement scanned or used an index.
  • Find, fix one thing, measure again.

Next: Server Configuration and Tuning, where we look at the settings that decide how much work the server can do.