MySQL’s slow query log records every statement that exceeds a configurable execution time threshold, providing an auditable record of the queries that hurt database performance most. Enabling it on a production WordPress server requires only two configuration changes and a log flush, with no downtime and negligible overhead at typical thresholds of one second. The long_query_time variable accepts fractional seconds, so setting it to 0.5 captures statements slower than 500 ms — a useful starting point before tightening to 0.25 or lower once obvious bottlenecks are resolved. The log_queries_not_using_indexes flag catches a separate class of problem: fast queries that perform full table scans, which stay fast only until the table grows large. MySQL’s mysqldumpslow tool aggregates the raw log, grouping similar queries and sorting by total execution time, average time, or call count. The output shows the top offenders with their literal SQL, making it straightforward to identify which WordPress plugin or theme function generated the problematic query. Once identified, running EXPLAIN SELECT ... in the MySQL console reveals whether the query performs a full table scan (type: ALL) or uses an index. Adding pt-query-digest from Percona Toolkit to the analysis workflow produces a more detailed report that includes percentile execution times and normalised query fingerprints across large log files. Rotating the slow query log with logrotate prevents unbounded disk growth — a weekly rotation with four weeks of retention is a reasonable default for most sites. The EXPLAIN and indexes guide covers the next step after identifying a slow query: adding the right composite index. The database bloat cleanup post addresses a root cause of many slow queries — oversized tables with orphaned rows. On shared hosting where you cannot edit my.cnf directly, the SET GLOBAL slow_query_log = ON statement enables the log at runtime if you have the SUPER or SYSTEM_VARIABLES_ADMIN privilege. Correlating slow query timestamps with WordPress error logs and traffic spikes in the access log produces a complete picture of when and why performance degrades.
Problem: Slow WordPress database queries are invisible without logging — administrators have no systematic way to identify which queries are degrading performance without the slow query log.
Solution: Enable MySQL's slow query log in my.cnf, set a half-second threshold, and use mysqldumpslow to aggregate the results and surface the worst offenders.
# /etc/mysql/my.cnf (or /etc/my.cnf) — enable slow query logging
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 1
min_examined_row_limit = 100
# After editing, reload MySQL without restart:
# mysql -u root -e "SET GLOBAL slow_query_log = ON; FLUSH SLOW LOGS;"
# Aggregate slow queries sorted by total execution time:
# mysqldumpslow -s t -t 20 /var/log/mysql/slow.log
# Rotate the log weekly (logrotate config)
# /etc/logrotate.d/mysql-slow
/var/log/mysql/slow.log {
weekly
rotate 4
missingok
compress
delaycompress
sharedscripts
postrotate
mysql -u root -e "SELECT SLEEP(0)" > /dev/null 2>&1 || true
mysql -u root -e "FLUSH SLOW LOGS;" > /dev/null 2>&1 || true
endscript
}NOTE: On XAMPP or local development machines set long_query_time = 0.1 to catch even faster queries — local hardware is faster than production, so queries that appear acceptable locally often exceed 500 ms on shared or cloud database hosts.