The MySQL slow query log is the most direct way to find database performance problems on a WordPress server. When enabled, MySQL writes every query that exceeds a configurable time threshold to a log file, including the query text, the execution time, the number of rows examined, and whether an index was used. On a live WordPress site, even a threshold of one second will surface the queries most worth investigating — and on a staging server with a copy of production data, lowering the threshold to 0.1 seconds reveals a much broader picture of inefficient queries. The two configuration variables that matter most are slow_query_log (enables the feature), slow_query_log_file (path to the log file), and long_query_time (the threshold in seconds, supports decimals). The optional log_queries_not_using_indexes variable logs every query that does a full table scan regardless of execution time — extremely useful for catching missing indexes on tables that are fast now but will degrade as data grows. Once you have a log file, mysqldumpslow (bundled with MySQL) summarises it by grouping similar queries and sorting by total time. For deeper analysis, Percona’s pt-query-digest tool provides a ranked report showing which query pattern consumes the most cumulative time across all executions. A query that takes 0.05 seconds but runs 10,000 times per hour costs more than a 2-second query that runs once a day. The workflow is: enable the log, wait for traffic, run pt-query-digest to find the top offender, reproduce it with EXPLAIN from the EXPLAIN guide, then add an index or rewrite the query. Review this alongside the server monitoring guide to correlate query load with CPU and I/O metrics.
Problem: WordPress database queries are slow but it is unclear which specific queries are the bottleneck, how often they run, and whether they are using indexes.
Solution: Enable the MySQL slow query log in my.cnf, then use mysqldumpslow to identify the worst offenders:
# /etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu)
# or /etc/my.cnf (CentOS/RHEL)
# Add under [mysqld]
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 100# Restart MySQL to apply config changes
sudo systemctl restart mysql
# Enable slow log at runtime without restart (takes effect immediately)
mysql -uroot -e "SET GLOBAL slow_query_log = 'ON';"
mysql -uroot -e "SET GLOBAL long_query_time = 1;"
mysql -uroot -e "SET GLOBAL log_queries_not_using_indexes = 'ON';"
# Verify settings are active
mysql -uroot -e "SHOW VARIABLES LIKE 'slow%';"
mysql -uroot -e "SHOW VARIABLES LIKE 'long_query_time';"
# Analyse with mysqldumpslow (bundled with MySQL)
# -s t = sort by total time, -t 10 = show top 10
sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# Analyse with pt-query-digest (Percona Toolkit)
sudo apt install percona-toolkit -y
sudo pt-query-digest /var/log/mysql/slow.log | head -n 80
# Rotate the slow log to start fresh
mysql -uroot -e "FLUSH SLOW LOGS;"NOTE: Setting long_query_time = 0 logs every single query — useful for a short profiling session on staging but never do this in production as the log will grow extremely fast and the disk write overhead will itself slow down the server. The min_examined_row_limit = 100 setting prevents the log from filling up with trivially small queries that happen to lack an index. On XAMPP or other local environments, the slow log configuration file is usually at /Applications/XAMPP/xamppfiles/etc/my.cnf on macOS. After identifying a slow query, open it in EXPLAIN and follow the indexes guide to add the right fix.