Using the MariaDB Slow Query Log to Find the Plugin Destroying Your WordPress Performance

A client's WooCommerce checkout had started timing out two or three times a day. Not constantly — just often enough to lose orders and frustrate customers. The store processed around 180 orders daily on a 4-core, 8GB VPS running CloudPanel, PHP 8.3-FPM, MariaDB 10.11, and nginx. Hardware wasn't the issue. Something was locking up the database intermittently, and I needed to find what.

The PHP-FPM slow log showed workers stacking up waiting on MySQL, but that only tells you PHP is waiting — not which query is the problem. The real diagnostic tool for this situation is the MariaDB slow query log. It records every query that exceeds a time threshold, along with execution time, rows examined, and the user that ran it. Here's the full process I followed.

Enabling the Slow Query Log Without a Restart

On a production server, I don't want to restart MariaDB just to enable logging. These variables are dynamic — I can set them on a live database:

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

The long_query_time = 0.5 threshold means anything over half a second gets logged. For a well-tuned WordPress site, every query should complete in under 100ms. Half a second is already a problem — but setting it lower on a busy site generates too much noise initially.

I also enable log_queries_not_using_indexes because these are queries that work fine on a small dataset but will eventually destroy performance as the site grows. Finding them early is preventive maintenance.

To make this survive a restart, I add it to the MariaDB configuration file:

[mysqld]
slow_query_log = 1
long_query_time = 0.5
log_queries_not_using_indexes = 1
slow_query_log_file = /var/log/mysql/slow.log
log_slow_verbosity = query_plan,explain

The log_slow_verbosity = query_plan,explain setting is MariaDB-specific (MySQL doesn't have it) and logs the query execution plan alongside each slow query. Invaluable for understanding why a query is slow without having to reproduce it manually.

Reading Raw Slow Log Output

After 24 hours, the slow query log had captured 847 entries. Most looked like this:

# Time: 240823 14:32:07
# User@Host: wordpress[wordpress] @ localhost []
# Thread_id: 4821  Schema: wp_store  QC_hit: No
# Query_time: 12.340621  Lock_time: 0.000184  Rows_sent: 1  Rows_examined: 2847391
SET timestamp=1724421127;
SELECT p.ID FROM wp_posts p
INNER JOIN wp_postmeta pm1 ON p.ID = pm1.post_id
INNER JOIN wp_postmeta pm2 ON p.ID = pm2.post_id
INNER JOIN wp_postmeta pm3 ON p.ID = pm3.post_id
WHERE p.post_type = 'product'
AND p.post_status = 'publish'
AND pm1.meta_key = '_visibility'
AND pm1.meta_value = 'visible'
AND pm2.meta_key = '_stock_status'
AND pm2.meta_value = 'instock'
AND pm3.meta_key = 'total_sales'
ORDER BY pm3.meta_value+0 DESC
LIMIT 4;

12.3 seconds. Examined 2.8 million rows to return 4 results. That's a full table scan across three JOINs on wp_postmeta — a table with 1.6 million rows on this site.

Using pt-query-digest for the Full Picture

Reading 847 log entries manually isn't practical. pt-query-digest from the Percona Toolkit aggregates similar queries and ranks them by total impact:

pt-query-digest /var/log/mysql/slow.log > /tmp/digest-report.txt

The output looks like this (truncated):

# Profile
# Rank Query ID                         Response time   Calls  R/Call  V/M
# ==== ================================ =============== ====== ======= ====
#    1 0xABC123DEF456 SELECT p.ID FR...  847.2341 68.2%    127  6.6711  3.21
#    2 0x789GHI012JKL SELECT SQL_CAL...  198.4520 16.0%     84  2.3625  1.87
#    3 0xMNO345PQR678 UPDATE wp_optio...   72.1100  5.8%    312  0.2311  0.14

Query #1 was responsible for 68% of all slow query time. 127 executions over 24 hours, averaging 6.7 seconds each. That's the triple-JOIN query I'd already spotted. Query #2 was a SQL_CALC_FOUND_ROWS on the posts table — a known WordPress pagination anti-pattern — taking 2.4 seconds average.

Tracing Queries Back to a Plugin

The slow query log doesn't tell you which plugin generated a query. WordPress itself doesn't tag queries with their origin (unlike frameworks like Laravel that can annotate queries). I needed another approach.

First, I checked SHOW FULL PROCESSLIST during a slow period to catch the query in flight:

SHOW FULL PROCESSLIST;

This showed the offending query running, but only with the database user — not the PHP code path. To trace it to a plugin, I used a two-step approach.

Step 1: Add query comments with the Savequeries constant

Temporarily in wp-config.php:

define('SAVEQUERIES', true);

Then I added a mu-plugin to tag queries with their call stack:

<?php
add_filter('query', function ($query) {
    if (stripos($query, 'total_sales') !== false) {
        $trace = wp_debug_backtrace_summary(null, 0, false);
        $caller = implode(' > ', array_slice($trace, 0, 5));
        error_log("SLOW_QUERY_TRACE: {$caller}");
    }
    return $query;
});

Within minutes, my error log showed:

SLOW_QUERY_TRACE: wc_get_top_sellers > WC_Widget_Top_Rated_Products->widget > 
dynamic_sidebar > suspended_starter_widget_area > starter_template_footer

Step 2: Confirm the plugin

The culprit was a "Popular Products" widget from a third-party theme add-on. It was running that triple-JOIN query on every single page load — not just on pages where the widget was displayed — because the theme loaded widget logic globally regardless of whether the sidebar was active on that template.

The Fix: A Composite Index and a Configuration Change

The immediate fix was twofold.

1. Add a composite index to wp_postmeta:

ALTER TABLE wp_postmeta ADD INDEX idx_meta_key_value (meta_key, meta_value(191));

This index lets MariaDB resolve the WHERE clauses on meta_key and meta_value without scanning the full table. The (191) prefix length keeps the index compatible with utf8mb4 columns under InnoDB's 767-byte key length limit.

After adding the index, the same query dropped from 12.3 seconds to 0.08 seconds:

EXPLAIN SELECT p.ID FROM wp_posts p
INNER JOIN wp_postmeta pm1 ON p.ID = pm1.post_id
...

+----+------+-------------------+---------+------+------+-------------+
| id | type | key               | key_len | ref  | rows | Extra       |
+----+------+-------------------+---------+------+------+-------------+
|  1 | ref  | idx_meta_key_value| 766     | const|   12 | Using where |
+----+------+-------------------+---------+------+------+-------------+

12 rows examined instead of 2.8 million.

2. Disable the widget's global loading:

I deactivated the theme add-on's global widget preloading by removing it from the active widgets and replacing it with a WooCommerce shortcode that only runs on the specific template where "popular products" were displayed. This eliminated the query from 95% of page loads entirely.

Setting Up Ongoing Monitoring

I don't leave the slow query log running indefinitely on production — it adds write I/O and the log file grows without rotation by default. Instead, I set up a logrotate entry and a monitoring alert:

/var/log/mysql/slow.log {
    daily
    rotate 7
    compress
    missingok
    notifempty
    create 0640 mysql mysql
    postrotate
        /usr/bin/mysqladmin flush-logs
    endscript
}

For alerting, I use a simple cron job that counts slow queries per hour and notifies if the count exceeds a threshold:

#!/bin/bash
COUNT=$(grep -c "^# Time:" /var/log/mysql/slow.log)
if [ "$COUNT" -gt 50 ]; then
    curl -s -o /dev/null "https://hc-ping.com/YOUR-CHECK-UUID/fail"
fi

This posts a failure to Healthchecks.io if more than 50 slow queries accumulate in a day. The threshold depends on the site — a busy WooCommerce store might have 5-10 legitimate slow queries from complex reports. More than 50 means something has changed.

The Broader Pattern

Every time I investigate a "my WordPress site is slow" complaint with no obvious cause, the slow query log is my second stop (after checking server resources and PHP-FPM status). In my experience maintaining 70+ sites, the pattern is almost always the same:

1. A plugin or theme runs a query that works fine on a fresh install with 500 posts
2. The site grows to 10,000+ posts and 500,000+ postmeta rows
3. The unindexed query that used to take 50ms now takes 5-12 seconds
4. The site owner blames hosting and upgrades their server (which never helps because the problem is I/O, not CPU or RAM)

The fix is nearly always an index, a plugin configuration change, or a plugin replacement. The slow query log is how you find it without guessing.

Stop Firefighting. Start Maintaining.

I manage 70+ WordPress sites for agencies and businesses. Database performance monitoring is part of every maintenance plan — I catch these problems before they start costing you orders.

If your WooCommerce store has intermittent slowdowns that your hosting provider can't explain, there's almost certainly an unindexed query hiding in your database. Get in touch and I'll find it.