Killing SQL_CALC_FOUND_ROWS on a 7-Million-Row Table

The users list in wp-admin took several seconds to load. Not on my laptop - on a high-traffic WordPress VIP site with a wp_users table north of seven million rows. Every editor who opened that screen paid the tax, and the database felt it too.

The slow query was hiding in plain sight, and the fix is a good lesson in how a convenient feature turns into a scaling problem.

The convenient feature that scans everything

WordPress list tables show “Showing 1-20 of 7,412,905.” To get that total, WP_User_Query leans on MySQL’s SQL_CALC_FOUND_ROWS:

SELECT SQL_CALC_FOUND_ROWS *
FROM wp_users
ORDER BY user_registered DESC
LIMIT 0, 20;

SELECT FOUND_ROWS();

That first hint looks harmless. What it actually tells MySQL is: compute the full result set as if there were no LIMIT, just so I can count it. On seven million rows, that’s a full scan on every single page load - to render a number almost nobody reads past.

Fix 1: stop counting exactly

The total barely changes minute to minute, and “of about 7.4 million” is just as useful to an editor as an exact figure. So drop the hint and cache an approximate count:

add_filter( 'query', function ( $query ) {
    // Only for the users-table count path.
    if ( str_contains( $query, 'SQL_CALC_FOUND_ROWS' )
        && str_contains( $query, "FROM {$GLOBALS['wpdb']->users}" ) ) {
        $query = str_replace( 'SQL_CALC_FOUND_ROWS', '', $query );
    }
    return $query;
} );

function approx_user_count(): int {
    $count = wp_cache_get( 'approx_total', 'users' );
    if ( false === $count ) {
        global $wpdb;
        // information_schema is instant; it never touches the table.
        $count = (int) $wpdb->get_var(
            "SELECT table_rows FROM information_schema.tables
             WHERE table_name = '{$wpdb->users}'"
        );
        wp_cache_set( 'approx_total', $count, 'users', 5 * MINUTE_IN_SECONDS );
    }
    return $count;
}

The paginated SELECT ... LIMIT 20 is now indexed and instant. The count comes from information_schema, which reads table metadata instead of the table, and it’s cached for five minutes on top of that.

Fix 2: delete the JOINs you didn’t ask for

With the scan gone, the query plan was finally readable - and it showed a JOIN against wp_usermeta that the screen didn’t actually use, dragged in by a well-meaning filter elsewhere. Every joined row multiplied the work. Removing it was a one-line change that cut the query cost again.

The lesson generalizes: on a big table, every JOIN is a promise you can afford it. Audit them.

Fix 3: the search box was worse

Then there was author search - a LIKE query behind an autocomplete:

SELECT * FROM wp_users
WHERE user_login LIKE '%jain%'
   OR display_name LIKE '%jain%';

A leading-wildcard LIKE cannot use an index. Every keystroke was another full-table scan across seven million rows. No amount of MySQL tuning fixes a leading %; the data structure is simply wrong for the job.

So we moved search off MySQL entirely and onto Elasticsearch, which is built for exactly this:

$results = $client->search( [
    'index' => 'users',
    'body'  => [
        'query' => [
            'multi_match' => [
                'query'  => $term,
                'fields' => [ 'user_login^3', 'display_name^2', 'user_email' ],
                'type'   => 'bool_prefix', // autocomplete-as-you-type
            ],
        ],
        'size'  => 10,
    ],
] );

Field boosts (^3, ^2) put login matches above display-name matches, and bool_prefix gives real as-you-type behavior. Multi-second scans became sub-100ms lookups.

What ties it together

Three fixes, one theme: stop asking the database to do work that doesn’t fit its shape. Don’t scan a table to count it. Don’t JOIN data you won’t show. Don’t ask a B-tree index to answer a query it structurally can’t. Each time, the win came from matching the tool to the access pattern - not from tuning the wrong tool harder.