Query Budgets and the Queries That Matter

The previous module produced numbers: a page takes 180 ms, the log holds 39 queries, one request in twenty is four times slower than the median. Those numbers describe a site. They do not tell you when to stop, or which of the 39 statements to open first.

A query budget answers the first question. It is a pair of numbers you write down for a page type and then defend: how many statements the request may issue, and how much database time it may spend. The first number you pick will be wrong, which is fine. Its value is that it turns "the site feels slow" into a test that fails.

Attribution answers the second. A query count is useless if you cannot name the plugin that produced it, and the count alone will point you at the wrong statement about half the time. This lesson builds both, then uses them on a page whose problem is not what it looks like.

A budget is a number for a page type, not for the site

Different page types legitimately cost different amounts. I measured four of them on a lab install: WordPress 7.0.7, PHP 8.5.6, MySQL 9.6.0, 32,003 published posts, no object cache, no page cache, query logging on.

Request	Queries	Database time	Slowest single
Front page, 10 posts	22	31.6 ms	25.9 ms
Single post	31	37.1 ms	27.5 ms
Search results, term matching 15,759 rows	22	46.2 ms	30.9 ms
REST /wp-json/wp/v2/posts, 10 items	10	29.9 ms	27.3 ms
Admin report page, 20 meta reads in a loop	39	7.1 ms	0.6 ms

Read that table twice. The front page and the search page issue the same number of statements and differ by half their database time. The page with the most statements has the least database time by a factor of six.

So the budget belongs to a page type, and the useful form is a test: front page, 30 statements and 60 ms; single post, 40 statements and 60 ms; search, 40 statements and 100 ms. Set the ceiling at roughly 1.3 times what you measured on a cold cache, then treat a breach as a signal to look rather than a build failure to panic about. Queries scale with content, so a budget without a dataset size attached to it is not a budget. Write the row count next to the number.

Thirty-nine statements is four shapes

The first pass over a log is unreadable because every statement carries different literals. A search query has the term in it, an IN list has a different set of ids on every page load. Group by shape instead: strip string and numeric literals, collapse whitespace, and count.

// Drop this in as wp-content/mu-plugins/query-budget.php, open a page
// with ?budget=1, and read the HTML comments at the end of the response.
// Requires define( 'SAVEQUERIES', true ); in wp-config.php.
add_action( 'shutdown', function () {
    if ( ! isset( $_GET['budget'] ) || empty( $GLOBALS['wpdb']->queries ) ) {
        return;
    }

    $log    = $GLOBALS['wpdb']->queries;
    $shapes = array();

    foreach ( $log as $entry ) {
        // Collapse literals and numbers so repeated statements group together.
        $shape = preg_replace( "/'(?:[^'\\\\]|\\\\.)*'/", "'?'", $entry[0] );
        $shape = preg_replace( '/\b\d+\b/', '?', $shape );
        $shape = trim( preg_replace( '/\s+/', ' ', $shape ) );

        $shapes[ $shape ]['count'] = ( $shapes[ $shape ]['count'] ?? 0 ) + 1;
        $shapes[ $shape ]['time']  = ( $shapes[ $shape ]['time'] ?? 0 ) + $entry[1];
    }

    printf( "\n<!-- queries=%d db=%.1fms -->\n", count( $log ), array_sum( wp_list_pluck( $log, 1 ) ) * 1000 );

    uasort( $shapes, fn( $a, $b ) => $b['time'] <=> $a['time'] );
    foreach ( array_slice( $shapes, 0, 5, true ) as $shape => $data ) {
        printf( "<!-- %4dx %7.1fms  %s -->\n", $data['count'], $data['time'] * 1000, substr( $shape, 0, 100 ) );
    }
}, 99 );

On the admin report page above, that snippet prints this. The order is by total time per shape, which is the second sorting you need and the one people forget:

<!-- queries=39 db=8.6ms -->
<!--   22x     3.9ms  SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (?) ORDER BY meta_id ASC -->
<!--    4x     1.1ms  SELECT option_value FROM wp_options WHERE option_name = '?' LIMIT ? -->
<!--    1x     1.0ms  SELECT option_name, option_value FROM wp_options WHERE autoload IN ( '?', '?', '?', '?' ) -->
<!--    1x     0.6ms  SELECT wp_posts.ID FROM wp_posts WHERE ?=? AND ((wp_posts.post_type = '?' AND (wp_posts.post_status -->
<!--    2x     0.5ms  SELECT wp_posts.ID FROM wp_posts WHERE ?=? AND wp_posts.post_name IN ('?') AND ( ? = ? ) AND wp_post -->

Thirty-nine statements, four shapes, and one of them is 22 of the 39. That is the whole benefit of the normalisation step: it converts a list into a ranking.

The busiest query is rarely the one that costs the most

On the report page the busiest shape is SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (?) at 22 executions and 4.2 ms. On the front page the busiest shape is the same statement at 10 executions, while the single most expensive statement in the request is the main query at 25.9 ms.

Those point at different work. Optimising the busiest shape means fixing a loop. Optimising the costliest statement means changing an index, a query shape or the amount of data the query touches. Both are real, and conflating them sends you to the wrong place: the front page does not need its postmeta loop tuned, it needs its main query looked at.

The costliest statement is often not a query WordPress wrote for a template. Search is the clearest case I have measured. On the same install, the term warehouse produced a request with 22 statements and 46 ms of database time, and one of those statements was 30.9 ms of it. That statement is the search itself, and it is doing a LIKE '%warehouse%' against the title, excerpt and content of every published post. There is no index to reach for, which is why the server walked 15,759 candidate rows. The lesson from that number is not a query rewrite. It is that search is a page type with its own budget, and on this site it is the one that needs the budget raised or the feature changed.

The caller in $wpdb->queries names functions, not plugins

Index three of a logged query is a comma-separated list of the calls in the stack at the moment the statement ran. It looks like attribution and it is not. On a single post the list for the postmeta query reads:

require('wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts,
update_post_caches, update_postmeta_cache, update_meta_cache

Every entry is a function name. There is no file path anywhere in it, so a query issued by a plugin and a query issued by core that both pass through update_meta_cache() are indistinguishable. If you have read advice about grepping that field for a plugin directory, that advice does not survive contact with the log.

To attribute properly, catch the statement on the query filter, walk a real backtrace, and key the result the same way wpdb stores it.

// wp-content/mu-plugins/query-attribution.php
global $wpsi_caller_by_sql;
$wpsi_caller_by_sql = array();

// wpdb collapses whitespace before it stores the statement in $wpdb->queries,
// so key the map from the collapsed form or the lookups will miss.
function wpsi_sql_key( $sql ) {
    return md5( preg_replace( '/\s+/', ' ', $sql ) );
}

add_filter( 'query', function ( $sql ) {
    global $wpsi_caller_by_sql;
    $key = wpsi_sql_key( $sql );

    foreach ( debug_backtrace( DEBUG_BACKTRACE_IGNORE_ARGS, 60 ) as $frame ) {
        $file = $frame['file'] ?? '';
        $pos  = strpos( $file, '/wp-content/' );
        if ( false !== $pos ) {
            $wpsi_caller_by_sql[ $key ] = substr( $file, $pos + 1 ) . ':' . $frame['line'];
            return $sql;
        }
    }

    $wpsi_caller_by_sql[ $key ] = '(core)';
    return $sql;
}, 0 );

With that in place, the 22 postmeta statements on the report page attribute like this:

  20x  wp-content/plugins/nplus-one-demo/nplus-one-demo.php:28
   2x  (core)

Twenty of the 22 come from one line of one plugin. Two are core priming the meta cache for the main query, which is correct behaviour and not a problem to fix. That split is what you need before you decide whether to touch anything.

SQL_CALC_FOUND_ROWS makes MySQL finish the work it could have skipped

no_found_rows is a WP_Query argument that defaults to false, and while it is false WordPress prefixes the main query with SQL_CALC_FOUND_ROWS. I took a real meta-sorted query from a lab install, with 32,001 matching rows over 172,007 postmeta rows, and ran it both ways in MySQL 9.6 with the buffer pool warm:

SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
WHERE ( wp_postmeta.meta_key = '_delivery_estimate_days' )
  AND wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish'
GROUP BY wp_posts.ID
ORDER BY wp_postmeta.meta_value+0 ASC
LIMIT 0, 24;

Variant	Run 1	Run 2	Run 3
With SQL_CALC_FOUND_ROWS	260 ms	240 ms	236 ms
Without it, no_found_rows => true	182 ms	188 ms	184 ms

Roughly a quarter of the work, removed by one argument. The mechanism is in the statement, not in WordPress: MySQL cannot stop after 24 rows when it has promised a total count, so it has to visit every row the WHERE clause matches. MySQL 9.6's own EXPLAIN ANALYZE says so out loud, in a line that is worth recognising:

-> Limit: 24 row(s) (no early end due to SQL_CALC_FOUND_ROWS)

Use it where the count is genuinely not needed. A related-posts block, a "latest three products" widget, a REST request for a fixed list, an internal loop that renders a known number of rows: all of these ask for a count nobody displays. Keep it on anything that renders pagination, because a paginated page without a total is a worse page, not a faster one.

An N+1 loop does not look slow, it looks numerous

Here is the trap in the report page. Its budget breach is a count breach, not a time breach: 39 statements against a 30-statement ceiling, and 7.1 ms of database time, which is the fastest page type in the table above. Nothing in the slow query log will show it, because no individual statement is slow.

The cause is a template that reads post metadata inside a loop over 20 posts. Each first read for a post fires:

SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (34772) ORDER BY meta_id ASC

One statement per post, 20 in total, each one cheap. The fix is to prime the cache once for the whole page before the loop asks for anything:

$query = new WP_Query( array(
    'post_type'              => 'post',
    'posts_per_page'         => 20,
    'fields'                 => 'ids',
    'no_found_rows'          => true,
    'update_post_meta_cache' => false,   // do not prime inside the main query
    'update_post_term_cache' => false,
) );

// One statement for all 20 posts, instead of one when each post is first read.
update_meta_cache( 'post', $query->posts );

Measured on the same page: 39 statements and 22 postmeta statements became 20 statements and 2, with the same HTML on the way out. That is what a budget is for. You would never have found this by looking for slow queries, and you would not have noticed it in a wall-clock measurement either, because 20 statements at a fifth of a millisecond each is four milliseconds on a page that already takes 180.

Two variants of the same mistake are worth recognising by their shape. The first is the loop over a serialised option: one get_option() per iteration, when wp_load_alloptions() or a single get_options() call would have covered the page. The second is a loop that calls get_post_meta() for a field that the main query already primed, which costs one statement per post for values that were in memory. Both look like a busy query in the log, and both are fixed by moving a read out of the loop rather than by indexing anything.

Start from the slowest request, not the busiest page

Traffic data points at the page with the most requests. Performance budgets should point at the page with the worst time, and the two lists are almost never in the same order. A product page viewed ten thousand times a day at 300 ms of database time costs more server time than a report viewed twice, and the report is still the one I would open first, for a reason worth stating: the report is failing at a rate that has nothing to do with how often it runs, and it will keep getting worse as the data grows.

The workflow I use is four steps, in this order.

1. Sort requests by p95 database time from the measurements in the previous module, not by request volume. Take the worst three.
2. Run the shape grouping on each of those three. Write down the statement count, the database time, and the top shape by count and by cost.
3. Classify the top shape. If its count scales with a loop length, it is an N+1 and no amount of indexing will help. If its cost is concentrated in one statement that scans, it is a query shape problem. If it is many small reads of the same row, it is a caching problem and it belongs in the next module.
4. Only then run EXPLAIN on the specific statement, and only on the one that is actually expensive.

The order matters because step 3 is cheap and step 4 is where people spend an afternoon. An index added to a query that is issued 20 times per request because of a loop makes the loop cheaper and the request no shorter.

Show the solution and the reasoning

The numbers depend on your install, so the reasoning is the part to check.

A budget sentence looks like this: "On a 32,000-post dataset with no object cache, the front page issues at most 30 statements and spends at most 60 ms in the database; the search page issues at most 40 statements and spends at most 100 ms." The dataset size is not decoration. A budget set on a 200-post development site will be breached by the same code on a 30,000-post site, and the breach will look like a regression when it is arithmetic.

The share question has a specific answer for most WordPress pages: one shape accounts for the majority of the statements, and it is usually a postmeta read or an option read. If the count tracks the number of posts in the loop, you have an N+1 and the fix is update_meta_cache() or update_post_meta_cache handling, not an index. If the count is fixed at two or three, the statement is part of the bootstrap and the fix is in the options table.

The busiest and costliest shapes are usually different files. On the pages measured here the busiest shape came from a theme template and the costliest came from WP_Query inside core, which is the normal arrangement and the reason step 3 of the workflow comes before EXPLAIN.

If your page shows a costliest shape with a single execution and a large share of the time, resist the urge to add an index. Run EXPLAIN first and read the rows column. A statement reading 15,000 rows for 10 results has a query shape problem, and the fix might be a better filter rather than a new index. A statement reading 12 rows and taking 30 ms has a lock or I/O problem, and an index will not touch it. Those two diagnoses are cheap to tell apart and expensive to confuse.