MySQL Slow Query Log Analysis and Optimization Workflow

MySQL’s slow query log records every query that takes longer than long_query_time seconds. For WordPress, it is the definitive tool for finding the N+1 queries, missing indexes, and poorly-written plugin queries that degrade performance at scale — before they become production incidents.

Problem: The MySQL slow query log produces a raw list of individual slow queries — on a busy WordPress server with thousands of unique queries, identifying the highest-impact queries to optimise requires manual correlation that the log does not provide.

Solution: Enable the slow query log with slow_query_log = ON, long_query_time = 1, and log_queries_not_using_indexes = ON in my.cnf. Analyse with pt-query-digest to group queries by fingerprint and rank by total execution time. Use EXPLAIN on the top queries, then add or adjust indexes accordingly.

The examples below enable and configure the slow query log, use mysqldumpslow to summarise the worst offenders, run pt-query-digest for advanced analysis, and show the workflow for fixing a slow WooCommerce meta query.

# /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
# Enable slow query log
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/slow.log
long_query_time         = 1       # log queries taking > 1 second
                                  # lower to 0.1 on staging to catch more
log_queries_not_using_indexes = 1 # log index-less queries even if fast
min_examined_row_limit  = 100     # ignore trivially small queries
log_slow_extra          = 1       # MySQL 8.0.14+: adds thread_id, bytes_sent etc.

# Apply without restart (MySQL 5.7+ supports dynamic slow log config)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

# Verify
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

Analyse and fix slow queries from the log:

# ── MYSQLDUMPSLOW ── (ships with MySQL)
# -s t: sort by total execution time
# -t 10: show top 10 queries
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# Output example:
# Count: 4820  Time=3.21s (15476s)  Lock=0.00s (2s)  Rows=1.0 (4820)
# SELECT * FROM ynYYAa7bFT_postmeta WHERE meta_key='_price' ORDER BY meta_value+0 DESC LIMIT N

# ── PT-QUERY-DIGEST ── (Percona Toolkit — much richer analysis)
pt-query-digest /var/log/mysql/slow.log | head -100

# Output includes fingerprints, percentiles, response time distribution:
# Rank  Response time  Calls  R/Call  V/M  Item
# ====  =============  =====  ======  ===  ====
#    1  15476.3 71.2%   4820   3.21   0.8  SELECT wp_postmeta

# Filter to queries on a specific table:
pt-query-digest --filter '$event->{fingerprint} =~ m/postmeta/' /var/log/mysql/slow.log

# ── FIX A SLOW QUERY ──
# Problem query: ORDER BY meta_value+0 (type conversion, no index)
EXPLAIN SELECT post_id FROM ynYYAa7bFT_postmeta
WHERE meta_key = '_price'
ORDER BY meta_value + 0 DESC
LIMIT 20;
-- type: ALL (full table scan), no index on meta_value+0

-- Solution: GENERATED COLUMN with an index
ALTER TABLE ynYYAa7bFT_postmeta
  ADD COLUMN price_num DECIMAL(10,2) GENERATED ALWAYS AS (
    CASE WHEN meta_value REGEXP '^[0-9]+(\\.[0-9]+)?$'
         THEN CAST(meta_value AS DECIMAL(10,2)) END
  ) VIRTUAL,
  ADD INDEX idx_price (meta_key, price_num);

-- Now query uses the index:
SELECT post_id, price_num FROM ynYYAa7bFT_postmeta
WHERE meta_key = '_price'
ORDER BY price_num DESC
LIMIT 20;  -- type: ref, key: idx_price — fast!

NOTE: Run slow query analysis weekly during development and monthly in production. Set long_query_time=0.1 on a staging server to capture all queries, generate a representative traffic load (using a tool like k6 or ab), then analyse the log for patterns. The most common WordPress slow queries involve wp_postmeta ORDER BY, wp_options autoload bloat, and WooCommerce product queries without proper index coverage.