Setting Up MySQL Slow Query Log

If a WordPress site feels sluggish and PHP profiling comes back clean, the bottleneck is usually sitting one layer down — in the database. The slow query log is the single fastest way to find out which queries are actually eating your response time, instead of guessing. Here’s how to turn it on, read it, and act on what it tells you.

What the Slow Query Log Actually Does

MySQL (and MariaDB, which most managed WP hosts run today) can log every query that takes longer than a threshold you set — commonly 1 or 2 seconds, though for diagnosing WordPress you’ll often want it lower, down around 0.5 seconds. Each entry records the query text, execution time, rows examined, rows sent, and the database it ran against. Instead of guessing which plugin or theme function is slow, you get an exact list ranked by cost.

This is different from general query logging (which logs everything, including fast queries) — the slow log only captures what’s actually worth your attention, which keeps the file small enough to read.

Enabling It: Three Common Paths

1. Managed Hosting Dashboard

Hosts like Kinsta, WP Engine, and Cloudways expose slow query logging through their dashboard or support team rather than raw MySQL access — you typically request it or toggle it in the site’s performance/database panel. This is the easiest path if you’re on managed hosting, since you don’t have shell access to the my.cnf file directly.

2. VPS / Dedicated Server (Direct Config)

If you manage your own MySQL instance, edit the config file (usually /etc/mysql/my.cnf or a file in /etc/mysql/mysql.conf.d/) and add:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

Restart MySQL (sudo systemctl restart mysql) for the config to take effect. long_query_time is in seconds and accepts decimals, so 0.5 is valid if you want to catch borderline-slow queries too.

3. Runtime (No Restart Needed)

You can enable it live via SQL, which is useful for a quick diagnostic session without touching config files or restarting the service:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

This resets on the next MySQL restart, which makes it a good default for a one-off investigation rather than permanent monitoring.

Reading the Output

A raw slow log entry looks like this:

# Query_time: 3.219521  Lock_time: 0.000142 Rows_sent: 12  Rows_examined: 894213
SELECT * FROM wp_postmeta WHERE meta_key = '_some_key' AND meta_value = 'x';

The gap between Rows_examined and Rows_sent is the tell. Here, MySQL scanned nearly 900,000 rows to return 12 — that’s a missing index, not a slow server. Fixing it is usually an index addition, not a hardware upgrade.

Raw log files get unwieldy fast on a busy site. mysqldumpslow ships with MySQL and summarizes the log by query pattern and frequency:

mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

That sorts by total time and shows the top 10 offenders — usually the highest-leverage list you’ll get from a single command.

Log Analysis Tool Comparison

Tool	Best For	Learning Curve	Cost
mysqldumpslow	Quick CLI summary, ships with MySQL	Low	Free
pt-query-digest (Percona Toolkit)	Deep analysis, query fingerprinting, trend reports	Medium	Free
MySQL Workbench Performance Reports	Visual GUI review on your desktop	Low	Free
Managed host’s built-in APM (Kinsta, WP Engine)	No server access needed, ties queries to specific page loads	Low	Included in plan

What to Do With What You Find

• Missing index: if Rows_examined dwarfs Rows_sent, run EXPLAIN on the query and check whether the WHERE clause columns are indexed. Adding an index to wp_postmeta.meta_key or a custom table column is a common fix.
• Plugin culprit: if the same query pattern repeats across many slow-log entries, grep your plugins directory for the literal SQL fragment to identify the source, then look for a lighter alternative or a caching layer for that specific query.
• Autoloaded options bloat: a huge, frequent SELECT * FROM wp_options WHERE autoload='yes' is one of the most common WordPress-specific slow queries — it usually means a plugin is storing large blobs as autoloaded options. Object caching (Redis or Memcached) masks this rather than fixing it; pruning the offending option is the real fix.
• N+1 query patterns: dozens of near-identical queries differing only by an ID are a sign of a loop calling a query inside it instead of batching — flag this to whoever maintains the theme or plugin.

FAQ

Does the slow query log slow down the database itself?
Overhead is minimal at reasonable thresholds (1 second and above) since only a small fraction of queries ever get logged. Setting the threshold very low (under 0.1s) on a high-traffic site can add measurable I/O from the logging itself — keep it at 0.5s or higher for production monitoring.

Can I do this without SSH access?
Yes, on managed hosting — ask support to enable it, or use the platform’s built-in APM/database insights panel, which usually surfaces the same data without you touching a config file.

How long should I leave it running?
For a one-off diagnosis, a few hours during peak traffic is enough. For ongoing monitoring, many teams leave it on permanently with log rotation configured, since new plugins and content changes can introduce new slow patterns over time.

Does this replace a caching plugin?
No — caching prevents queries from running at all on repeat page views. The slow log helps you find and fix queries that are inherently expensive, including ones that run on every uncached request (like admin-ajax calls or logged-in-user views that bypass page cache).

Verdict

The slow query log is one of the few diagnostic tools that gives you a direct, ranked answer instead of a hunch. If you manage your own server, the setup is a five-minute config change; if you’re on managed hosting, it’s a support ticket or dashboard toggle away. Either way, run it for at least one busy day before assuming a performance problem needs more hardware — more often than not, it needs one missing index.