A WooCommerce client once described their admin area as “like wading through mud”. Product edits took eight seconds to save. The orders screen took ten seconds to load. Front-end pages were fine, because they were cached, but anything that touched the database directly was painfully slow. The hosting company suggested upgrading the server. Instead, we turned on the MySQL slow query log for a day. It showed that one plugin was running a single unindexed query against the postmeta table on every admin page load, scanning more than three million rows each time. Fixing that one query solved the problem. No upgrade needed.
The slow query log is one of the most useful diagnostic tools for WordPress performance, and one of the least used. It records every database query that takes longer than a threshold you set, along with how long it took and how many rows it examined. Instead of guessing which plugin is slow, you get evidence.
When to use it
Turn to the slow query log when:
- Admin pages, checkout, search or other uncached pages are slow
- Server CPU is high and MySQL is the busiest process
- Response times degrade as the database grows
- You want to check whether a new plugin adds expensive queries
It’s less useful for sites where every page is served from a full-page cache and the database is rarely touched by visitors. Even then, it often reveals slow background tasks.
Enabling the slow query log
You need access to the MySQL or MariaDB server, which means a VPS, dedicated server or a managed database with configuration options. On shared hosting, ask your host whether they can provide slow query data for your database.
You can enable logging temporarily without restarting MySQL:
That logs every query that takes longer than half a second. For a more permanent setup, add the settings to the MySQL configuration, typically /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/mysql/mariadb.conf.d/50-server.cnf:
Then restart the database service.
Choosing a threshold
The default threshold is ten seconds, which is far too high for a website. Anything that takes even one second on a web request is a serious problem. Start with 0.5 or 1 second to catch the worst offenders. Once those are fixed, you can lower it to 0.1 or 0.2 seconds to find smaller improvements.
Setting long_query_time to zero logs every query. That’s useful for short, focused investigations on staging, but on a busy production site it generates enormous log files quickly. Use it carefully and only for minutes at a time.
About log_queries_not_using_indexes
This option logs any query that doesn’t use an index, regardless of speed. On WordPress, it produces a lot of noise from small, harmless queries against tiny tables. Leave it off unless you’re investigating something specific.
Reading the log
Each entry in the slow log looks roughly like this:
The key numbers are:
- Query_time: how long the query took
- Lock_time: time spent waiting for locks, which indicates contention
- Rows_sent: rows returned
- Rows_examined: rows the database had to look at
A large gap between rows examined and rows sent is the classic sign of a missing index or an inefficient query. In the example, MySQL looked at more than three million rows to return twelve.
Summarising with pt-query-digest
Reading raw slow logs gets tedious fast. Percona Toolkit’s pt-query-digest groups similar queries together and ranks them by total time:
The report starts with a ranking of query patterns by their share of total time, followed by details for each one: how many times it ran, average and maximum time, rows examined and an example. Focus on the top few entries. Often, one or two query patterns account for most of the slow time.
Total time matters more than individual duration. A query that takes 0.3 seconds but runs ten thousand times a day costs far more than one that takes five seconds once a week.
Tracing a query back to its source
The slow log tells you which query is slow but not which plugin or theme ran it. To trace it:
- Look at the tables and meta keys. Custom tables often have the plugin’s prefix, like wp_wc_ for WooCommerce or wp_yoast_ for Yoast. Meta keys and option names often identify the plugin too.
- Use Query Monitor. This free plugin shows every query on a page load, with timing and the function and plugin that called it. Load the slow page while logged in as an administrator and look for the matching query.
- Search the codebase. Grep for distinctive parts of the query, such as a meta key name, in wp-content.
Common slow query patterns in WordPress
Meta queries on large postmeta tables
Queries that filter or sort by post meta are the most common slow queries in WordPress. The postmeta table has an index on meta_key but not on meta_value, so filtering by value often scans many rows. WooCommerce stores, directories and sites using custom fields for filtering are particularly affected.
Fixes include using taxonomies instead of meta for filterable attributes, storing data in custom tables, adding targeted indexes and caching results.
Autoloaded options
WordPress loads all autoloaded options on every request with a single query. If that data is several megabytes, the query is slow and memory heavy. It shows up in the slow log as a SELECT on wp_options with autoload conditions.
Transients and expired data
Some plugins store large amounts of data in transients in the options table, and expired transients pile up when there’s no persistent object cache. Queries against a bloated options table slow down.
Post queries with ORDER BY RAND()
Random ordering forces MySQL to process every matching row. Plugins that show “random posts” or “random products” this way become slow as content grows.
Large unindexed custom tables
Analytics, logging and statistics plugins often write to custom tables that grow to millions of rows. Queries against them without proper indexes become very slow.
Comment and search queries
Default WordPress search uses LIKE queries across post content, which can’t use normal indexes. On large sites, search becomes slow. Dedicated search solutions or full-text indexes help.
Fixing what you find
Depending on the cause:
- Update the plugin. Newer versions may have fixed the query.
- Report it to the developer with the query and the row counts. Good developers appreciate it.
- Replace the plugin if it’s abandoned or fundamentally inefficient.
- Add an index for queries you can’t change. Test on staging first and measure the improvement. Indexes speed up reads but add a little cost to writes.
- Add a persistent object cache such as Redis, which reduces repeated queries.
- Clean up data: delete expired transients, trim log tables and reduce autoloaded options.
Use EXPLAIN to understand how MySQL executes a query before and after changes:
Look at the type column, where ALL means a full table scan, the key column, showing which index is used, and rows, the estimated rows examined.
Turn it off or rotate it
The slow log grows continuously. If you leave it enabled permanently, make sure it’s included in logrotate. Otherwise, turn it off after your investigation:
Revisit the slow query log every few months, and after adding major plugins. A short session with it often finds improvements that no amount of front-end tuning could.
Building it into routine maintenance
The slow query log is most valuable when it’s used regularly rather than only during emergencies. A simple routine works well: enable it with a one-second threshold for a full day each month, run pt-query-digest on the result and compare the top entries with the previous month. New entries usually correspond to a plugin that was added or updated, or to a table that has grown past the point where a query stays fast.
Keep the reports. Over time, they show how the database is evolving and give you evidence when you need to justify a change, such as replacing a slow plugin or investing in a larger database server. They’re also useful when talking to plugin developers, because a report showing a query’s average time, frequency and rows examined is far more persuasive than “your plugin feels slow”.
A note on shared database servers
If several sites share one database server, a slow query from one site affects every other site, because it competes for the same CPU, memory and disk. The slow log shows the database user for each query, which makes it easy to see which site is responsible. On shared setups, that alone can settle long-running debates about why everything slows down at certain times of day.
Frequently asked questions
Does the slow query log slow down MySQL?
With a sensible threshold, the overhead is minimal, because only slow queries are written. Logging every query has a noticeable cost on busy servers.
Can I use it on managed database services?
Most managed MySQL services, such as those from cloud providers, expose slow query logs through their dashboards or logging services.
What threshold should I use for WordPress?
Start at 0.5 or 1 second. Once the worst queries are fixed, lower it to 0.1 or 0.2 seconds for finer tuning.
Is Query Monitor enough without the slow log?
Query Monitor is excellent for individual page loads you can reproduce. The slow log catches queries from all requests, including cron jobs, AJAX calls and visitors you never see.
