Skip to content
DBAExperts
MySQL Diagnostics

How to find slow queries in MySQL

Before optimizing, you have to measure. MySQL 8.0 offers three complementary tools: the slow query log records queries that take too long, EXPLAIN shows a query plan, and performance_schema aggregates statistics for all queries on the server.

  1. 1

    Enable the slow query log

    It records queries that take longer than the threshold. long_query_time is in seconds (decimals allowed). Turn it on live so you do not have to restart.

    SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;   -- consultas de más de 1 segundo
  2. 2

    See which queries consume the most total time

    performance_schema sums up the cumulative impact. Sort by total time, not just time per execution: a fast query that runs thousands of times can weigh more than an occasional slow one.

    SELECT digest_text, count_star, 
       ROUND(sum_timer_wait/1e12, 2) AS seg_total
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;
  3. 3

    Analyze the plan of the suspect query

    EXPLAIN tells you how MySQL plans to execute it. Look for type ALL (full scan) and high values in rows.

    EXPLAIN SELECT * FROM pedidos WHERE estado = 'pendiente';
  4. 4

    Measure the real plan with EXPLAIN ANALYZE

    In MySQL 8.0.18 and later, EXPLAIN ANALYZE runs the query and shows real timings per step. Careful: it does run the query, so do not use it on statements that modify data.

    EXPLAIN ANALYZE SELECT * FROM pedidos WHERE estado = 'pendiente';

// common mistake

A very low long_query_time (0, for example) fills the disk with logs very quickly on a busy server. Start with 1 or 2 seconds, identify the worst offenders, and lower the threshold only if you need to.

// when it's worth an expert

When the app is slow but you cannot tell which query is to blame, or you found it but it will not budge no matter how you optimize, an expert eye helps. At dba.mx we do performance diagnostics with real data, at a fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.