Reading slow_query_log on shared hosting: four patterns

A Tuesday afternoon, a site you took over from another freelancer last month, and the host's CPU graph is spiking every ninety seconds. You poke around in cPanel, find phpMyAdmin, and the homepage takes four seconds to first byte. You'd love to attach a profiler, but you don't have shell access. What you do have is slow_query_log (the host left it on, or you flipped it on yourself) and 12,000 lines from the last 24 hours sitting in /home/oldsite/logs/.

Most of those lines look the same. That's the good news. On a legacy site, four query patterns explain roughly 80% of what slow_query_log shows you. The other 20% is genuinely interesting work; the four below are usually a fifteen-minute fix once you can read them.

Where the log lives when you don't have root

On shared hosting, the slow query log almost never sits at /var/log/mysql/. Hosts redirect it per-account because they don't want you seeing other tenants' queries. The two real locations you will see are:

# cPanel / DirectAdmin / Plesk
/home/<account>/logs/slow_query.log
~/tmp/slow_query.log

# Managed WP hosts (often disabled, sometimes read-only)
/var/log/mysql-slow.log

If the file is empty, check long_query_time. On shared hosting the default is usually 2.0 seconds, and a query that hammers MySQL 400 times a minute at 1.2 seconds each will never show up. If you can run SQL, drop the threshold temporarily:

SET GLOBAL long_query_time = 0.5;
SET GLOBAL slow_query_log = 'ON';

Most shared hosts refuse SET GLOBAL from an unprivileged user. If yours does, file a ticket and ask them to set it for your account. This is a routine request and they have a runbook. The MySQL reference manual covers the variables in full if support pushes back.

Once it's flowing, read the log raw before reaching for mysqldumpslow or pt-query-digest. You want to feel the shape of the noise first.

Pattern 1: autoload that ate the database

The single most common slow query on a WordPress site running for more than three years:

SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

This runs on every request. It should return a few hundred kilobytes. On the site we're describing, it returned 47 MB.

The cause is almost always a plugin that writes to wp_options with autoload = 'yes' for things that should not autoload: transients that forgot to expire, large arrays of cached API responses, an analytics plugin's seven-day rolling stats. The query itself is fine. The data is what's broken.

EXPLAIN will look healthy:

+----+-------------+------------+------+----------+------+
| id | select_type | table      | type | key      | rows |
+----+-------------+------------+------+----------+------+
|  1 | SIMPLE      | wp_options | ref  | autoload | 1432 |
+----+-------------+------------+------+----------+------+

The index is being used. The problem is the bytes returned. Find the offenders:

SELECT option_name, LENGTH(option_value) AS bytes
FROM wp_options
WHERE autoload = 'yes'
ORDER BY bytes DESC
LIMIT 20;

Anything over 100 KB is suspect. Anything over 1 MB is almost always wrong. For each one, identify the plugin, then either disable autoload or delete the row:

UPDATE wp_options SET autoload = 'no' WHERE option_name = 'gravityform_cache_meta';

Take a backup first. wp_options is the easiest WordPress table to brick. The wp_load_alloptions reference is worth a look if you want to understand how WordPress reads the result on each request.

Pattern 2: ORDER BY post_date, no covering index

The second most common pattern, from the same site:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
WHERE wp_posts.post_type = 'product'
  AND wp_posts.post_status = 'publish'
ORDER BY wp_posts.post_date DESC
LIMIT 0, 12;

WooCommerce shop page, default sort by date. 0.9 seconds per call. There are 84,000 products. The default WordPress index on wp_posts covers (post_type, post_status, post_date, ID) and is named type_status_date. If it's missing (a previous developer dropped it, or the table was rebuilt from a partial dump), MySQL falls back to a filesort:

Extra: Using where; Using filesort

That word, "filesort", is your signal. It means MySQL is sorting the result set after reading it, which on a large table is slow. Add the index back:

ALTER TABLE wp_posts
ADD INDEX type_status_date (post_type, post_status, post_date, ID);

Run during a quiet hour. On InnoDB with MySQL 5.7 or newer this is an online operation, but it still chews IO and locks briefly at the start and end.

While you're there, kill SQL_CALC_FOUND_ROWS if the calling code lets you. WordPress core uses it for pagination counts, but it forces MySQL to materialise the full result set, which roughly doubles the cost. A separate SELECT COUNT(*) is faster on any table over a few thousand rows.

Pattern 3: LIKE '%term%' against post_content

SELECT * FROM wp_posts
WHERE post_status = 'publish'
  AND (post_title LIKE '%boiler%' OR post_content LIKE '%boiler%');

Search box. Two seconds. Every time.

Leading-wildcard LIKE cannot use a B-tree index. It always scans. On a site with 40,000 posts and an average post_content of 8 KB, that's reading roughly 300 MB of data per search. The fix is not an index. The fix is to stop searching with LIKE.

Three options, in order of effort:

1. Install Relevanssi or SearchWP. They build their own index table. Five minutes, works.
2. MySQL FULLTEXT index. Native, no plugin. Works well for Latin scripts; less good for CJK without a tokeniser. ALTER TABLE wp_posts ADD FULLTEXT(post_title, post_content); and rewrite the query to MATCH() AGAINST().
3. External search (Algolia, Meilisearch, Typesense). Overkill for most legacy sites.

If you can't change the search at all, at least narrow the scope:

AND post_type IN ('post', 'page', 'product')
AND post_date > '2022-01-01'

Smaller scan, same UX for 95% of queries.

Pattern 4: the plugin loop that becomes N+1

This one doesn't look like a slow query. It looks like 600 identical fast queries.

SELECT meta_value FROM wp_postmeta WHERE post_id = 12847 AND meta_key = '_stock_status';
SELECT meta_value FROM wp_postmeta WHERE post_id = 12848 AND meta_key = '_stock_status';
SELECT meta_value FROM wp_postmeta WHERE post_id = 12849 AND meta_key = '_stock_status';
... 597 more

Each one takes 3 ms. Total: 1.8 seconds on one page render. None of them cross long_query_time = 0.5, so they never appear in the slow log at all. You find this by setting long_query_time = 0 for one minute and watching, or by enabling Query Monitor for a single logged-in admin session.

The cause is always the same shape: a loop iterating posts, calling get_post_meta() per row, with no update_meta_cache() prime and no 'update_post_meta_cache' => true on the parent get_posts(). A WooCommerce shortcode that lists products by stock. A custom widget that pulls last-updated dates. A theme footer that counts comments per category.

Fix at the application layer:

$ids = wp_list_pluck( $products, 'ID' );
update_meta_cache( 'post', $ids ); // one query, primes the cache
foreach ( $products as $product ) {
    $stock = get_post_meta( $product->ID, '_stock_status', true );
    // ...
}

One query instead of 600. The slow log goes quiet on its own as soon as the buffer pool stops thrashing.

The order to triage in

Open the log, group identical queries by fingerprint, and ask of each cluster: how much wall-clock time per hour does this consume? Pattern 1 usually answers itself within thirty seconds of looking at wp_options. Pattern 2 is one ALTER TABLE. Pattern 3 is a plugin install. Pattern 4 needs a developer, but you can rule it in or out by enabling Query Monitor on a staging copy and reloading the homepage.

If you fix the autoload first, almost every other pattern gets slightly faster as a side effect. Less pressure on the InnoDB buffer pool means more of wp_posts stays cached in memory, which means filesorts and full scans do less physical IO.

Today's smallest move

Pull yesterday's slow_query_log to your laptop, sort the top 20 queries by total wall-clock time, and check whether the first cluster matches Pattern 1. If it does, you have a fix you can ship inside an hour, and one UPDATE away from a faster homepage. When we built Pier we ran into this on roughly every other site we docked into. The way we ended up handling it was a slow-query view that joins the log against table sizes and the MySQL editor in one pane, with version history on every UPDATE so reverting a wrong autoload toggle is one keystroke.