How to Find and Fix Slow MySQL Queries in WordPress

Beyond "install Query Monitor"

Every WordPress performance guide tells you to install Query Monitor. It's good advice. Query Monitor is the best tool for seeing what's happening inside WordPress at the database level. It shows you every query, how long it took, and which component (theme, plugin, core) generated it.

But Query Monitor has limits. It shows you the symptom (this query took 1.2 seconds) without explaining the cause (why it took 1.2 seconds) or the fix (what to change so it takes 0.05 seconds).

For that, you need to go deeper. You need the slow query log, the EXPLAIN command, and an understanding of how MySQL indexes work. None of this is complicated. It just requires tools that most WordPress tutorials don't cover.

Here's the process I use on every client site when database performance needs to be diagnosed.

Step 1: install Query Monitor (the starting point)

If you don't have it already, install the Query Monitor plugin. It's free, it's maintained by a WordPress core contributor, and it's the best diagnostic tool in the ecosystem.

After activation, you'll see a new admin bar item showing the page generation time, query count, and peak memory usage. Click it to see the full breakdown.

What to look for

Go to the Queries panel and sort by time (slowest first). You're looking for:

• Queries over 0.1 seconds (100ms). These are your candidates for optimization.
• Queries with high row counts. A query returning 50,000 rows to display 10 products means something is wrong with the query logic.
• Duplicate queries. The same query running 5 times on a single page load means a plugin is calling it in a loop instead of batching.
• Queries by component. Sort by "Component" to see which plugin or theme is responsible for the slowest queries.

Write down the slow queries. You'll need them for the next step.

Step 2: enable the slow query log

Query Monitor only captures queries during your browsing session. It misses:

• Queries from logged-out visitors (caching may hide these)
• Queries from WooCommerce background tasks (Action Scheduler, cron jobs)
• Queries from REST API requests
• Queries that only run under load (when the database is handling many connections simultaneously)

The slow query log captures everything. It's a MySQL/MariaDB server feature that logs every query exceeding a time threshold to a file.

Enable it

SSH into your server and connect to MySQL/MariaDB as root or a user with SUPER privileges:

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

This logs every query taking longer than 0.5 seconds. For initial investigation, you can lower this to 0.1 seconds, but be prepared for a large log file on busy sites.

Make it permanent

Add these to your MySQL/MariaDB configuration file (/etc/mysql/my.cnf or /etc/mysql/mariadb.conf.d/50-server.cnf):

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

The log_queries_not_using_indexes option is useful. It logs queries that do full table scans, even if they complete quickly. These queries are fast now but will get slower as your tables grow.

Restart MySQL/MariaDB for permanent settings to take effect:

sudo systemctl restart mysql
# or
sudo systemctl restart mariadb

Read the log

Let it run for 24-48 hours of normal traffic, then check the log:

sudo tail -100 /var/log/mysql/slow-query.log

Each entry shows:

• When the query ran
• How long it took
• How many rows it examined vs. how many it returned
• The full SQL query text

Look for the ratio of rows examined to rows returned. A query that examines 300,000 rows to return 24 is doing a full table scan. That's your optimization target.

Use mysqldumpslow for analysis

If the log is large, MySQL ships with a summary tool:

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

This shows the top 10 slowest queries, grouped by pattern. The -s t flag sorts by total time, so you see which queries cost the most overall (frequency times execution time).

Step 3: diagnose with EXPLAIN

EXPLAIN is the most useful MySQL command that most WordPress developers have never used. It shows you the query execution plan: how MySQL plans to find the data, which indexes it will use (or not use), and how many rows it expects to scan.

Take one of the slow queries from Step 1 or 2 and put EXPLAIN in front of it:

EXPLAIN SELECT p.ID, p.post_title, pm.meta_value as price
FROM wp_posts p
JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE p.post_type = 'product'
  AND p.post_status = 'publish'
  AND pm.meta_key = '_price'
ORDER BY pm.meta_value+0 ASC
LIMIT 24;

Reading the EXPLAIN output

The output is a table with several columns. The ones that matter most:

type (the access method):

• ALL: full table scan. This is bad. MySQL reads every row in the table.
• index: full index scan. Better than ALL, but still reads the entire index.
• range: index range scan. MySQL uses an index to find a range of rows. Good.
• ref: index lookup. MySQL uses an index to find matching rows directly. Very good.
• eq_ref: unique index lookup. The best possible join type.
• const/system: the query matches at most one row. Optimal.

If you see type: ALL on a table with 300,000 rows, that's your problem.

rows (estimated rows to examine): This is how many rows MySQL thinks it needs to scan. If this number is orders of magnitude larger than the number of rows you actually want returned, the query is inefficient.

key (the index used): If this is NULL, MySQL isn't using any index for this table. That usually means a full table scan, and it's often the root cause of slow queries.

Extra (additional information):

• Using filesort: MySQL needs to sort the results in a separate pass. Can be slow on large result sets.
• Using temporary: MySQL creates a temporary table to process the query. Usually means the query could be restructured.
• Using where: MySQL applies a WHERE filter after reading rows. Normal, but if combined with type: ALL, it means filtering after a full scan.
• Using index: MySQL can answer the query entirely from the index without reading the actual table data. This is the fastest possible execution.

A real example

Here's an EXPLAIN from a client's WooCommerce store. The query was loading a shop page and filtering products by price:

+----+-------+------+----------+------+---------+
| id | table | type | key      | rows | Extra   |
+----+-------+------+----------+------+---------+
|  1 | p     | ref  | type_... | 2847 | Using   |
|  1 | pm    | ALL  | NULL     |327891| Using   |
+----+-------+------+----------+------+---------+

The wp_posts table (p) used an index and found 2,847 products. Fine. But the wp_postmeta table (pm) did a full table scan: type: ALL, key: NULL, rows: 327,891. MySQL read every single row in wp_postmeta to join it with the product rows.

This is what a 2-second query looks like in EXPLAIN output.

Step 4: fix with targeted indexes

Most slow WordPress queries are fixed by adding one or two indexes. You don't need to rewrite the queries themselves (and in most cases you can't, because they're generated by WordPress core or plugins).

The wp_postmeta meta_value index

This is the highest-impact index for WooCommerce stores:

ALTER TABLE wp_postmeta ADD INDEX meta_value_idx (meta_value(191));

This covers queries that filter or sort by meta_value (product prices, stock statuses, SKUs). Without it, every price filter and product sort does a full table scan.

A composite index for common WooCommerce queries

WooCommerce frequently queries wp_postmeta by both post_id and meta_key together. WordPress has separate indexes on each column, but a composite index on both is faster:

ALTER TABLE wp_postmeta ADD INDEX post_id_meta_key (post_id, meta_key(191));

This covers the pattern: "get meta_key X for post_id Y." WordPress does this constantly when loading product data.

The wp_options autoload index

WordPress queries wp_options with WHERE autoload IN ('yes','on') on every page load. A targeted index helps:

ALTER TABLE wp_options ADD INDEX autoload_idx (autoload, option_name);

This is less impactful than the wp_postmeta indexes but helps on sites with large wp_options tables (5,000+ rows).

Verify the fix with EXPLAIN

After adding an index, run the same EXPLAIN query again. You should see:

• type change from ALL to ref or range
• key change from NULL to the name of your new index
• rows drop from hundreds of thousands to a much smaller number

On the client store from the example above, after adding the meta_value_idx index:

+----+-------+------+-----------------+------+---------+
| id | table | type | key             | rows | Extra   |
+----+-------+------+-----------------+------+---------+
|  1 | p     | ref  | type_...        | 2847 | Using   |
|  1 | pm    | ref  | meta_value_idx  |   38 | Using   |
+----+-------+------+-----------------+------+---------+

rows went from 327,891 to 38. The query went from 2.1 seconds to 0.08 seconds.

Step 5: find and fix duplicate queries

Query Monitor's "Duplicate Queries" panel shows queries that run multiple times on the same page load. A common pattern on WooCommerce sites:

SELECT meta_value FROM wp_postmeta WHERE post_id = 1234 AND meta_key = '_price'
SELECT meta_value FROM wp_postmeta WHERE post_id = 1235 AND meta_key = '_price'
SELECT meta_value FROM wp_postmeta WHERE post_id = 1236 AND meta_key = '_price'
-- ... repeated for every product on the page

This is the N+1 query problem. Instead of fetching all prices in one query, a plugin or theme fetches them one at a time.

How to fix it

This usually comes from a theme or plugin that calls get_post_meta() in a loop instead of using update_meta_cache() or wp_cache_get(). Your options:

1. Enable an object cache (Redis or Memcached). This caches meta lookups in memory so duplicate queries are served from cache instead of hitting the database. Doesn't fix the root cause but removes the performance cost.

2. Report it to the plugin/theme developer. N+1 queries are a code quality issue. The developer should batch the lookups.

3. Use WooCommerce product lookup tables. These reduce the number of meta queries WooCommerce needs to run. See the wp_postmeta optimization guide for details.

Step 6: optimize database tables

After making changes (adding indexes, deleting data), optimize your tables to ensure MySQL uses the updated index statistics:

ANALYZE TABLE wp_postmeta;
ANALYZE TABLE wp_posts;
ANALYZE TABLE wp_options;

ANALYZE TABLE updates the index statistics without rebuilding the table. It's fast and non-blocking.

If you've deleted large amounts of data (cleaned orphaned postmeta, purged revisions), also run:

OPTIMIZE TABLE wp_postmeta;
OPTIMIZE TABLE wp_posts;

OPTIMIZE TABLE rebuilds the table file and reclaims disk space. It's slower and locks the table briefly, so run it during off-peak hours on production sites.

MariaDB vs MySQL: why it matters for WordPress

Most managed WordPress hosts have switched from MySQL to MariaDB. If you manage your own server, you should consider it too. MariaDB is a fork of MySQL that's fully compatible with WordPress but has several advantages for WordPress-style workloads.

Better query optimizer

MariaDB's optimizer handles subqueries and JOINs more efficiently than MySQL in many WordPress scenarios. A common WordPress pattern:

SELECT * FROM wp_posts WHERE ID IN (
  SELECT post_id FROM wp_postmeta WHERE meta_key = '_featured' AND meta_value = 'yes'
);

MySQL sometimes materializes the subquery as a temporary table (slow on large tables). MariaDB more often converts it to a semi-join (fast).

Thread pool

MySQL creates one thread per connection by default. Under heavy load (100+ simultaneous connections), thread creation overhead causes latency spikes. MariaDB's thread pool reuses a fixed number of threads across connections, handling concurrency more smoothly.

Aria storage engine for temporary tables

When MySQL needs a temporary table for sorting or grouping, it uses the MEMORY engine (limited by tmp_table_size) and falls back to InnoDB on disk when the table is too large. MariaDB uses Aria, which is crash-safe and faster for these temporary operations.

Switching from MySQL to MariaDB

On Debian/Ubuntu:

sudo apt install mariadb-server

The installer handles the migration. Your databases, users, and permissions carry over. WordPress doesn't know the difference; the connection settings are identical.

Test on staging first. While MariaDB is compatible, some edge-case query behaviors differ. In 3+ years of running MariaDB across all client sites, I've never hit a compatibility issue with WordPress or WooCommerce.

Ongoing monitoring

Keep the slow query log running

Set long_query_time to 1.0 seconds for ongoing monitoring (0.5 seconds generates too much log data on busy sites over time). Check the log weekly.

Set up log rotation

The slow query log grows indefinitely. Add a logrotate configuration:

/var/log/mysql/slow-query.log {
    weekly
    rotate 4
    compress
    missingok
    notifempty
    create 640 mysql mysql
    postrotate
        mysqladmin flush-logs
    endscript
}

This keeps 4 weeks of logs, compressed, and automatically rotates them.

Query Monitor in production

Query Monitor is safe to run on production sites. It only activates for logged-in administrators. Visitors never see it and it adds negligible overhead when inactive.

Use it for spot checks when you notice performance changes. A plugin update that adds a slow query will show up immediately in Query Monitor.

Related reading

• wp_postmeta is Destroying Your WooCommerce Store's Speed. The specific wp_postmeta optimization guide, including product lookup tables and HPOS migration.
• How to Clean Your wp_options Table (The Right Way). Database cleanup that removes the dead data before you start tuning queries.
• WordPress Slow After 2 Years? Here's What's Actually Wrong. The full diagnostic covering all 5 common causes of WordPress slowdown.
• Action Scheduler Table Bloat: The Hidden WordPress Performance Killer. The WooCommerce background task table that quietly accumulates hundreds of thousands of rows.