SQL_CALC_FOUND_ROWS in WordPress

Background

When presenting so-called “archive” pages of content to users WordPress uses the notorious SQL_CALC_FOUND_ROWS MySQL extension to populate its display that looks something like this. This kind of user interface is known as “pagination” because it lets the user select pages of results. So, it’s really useful.

The phrase “Showing 1-12 of 1348 results” clearly shows the information WordPress needs to get from its database. It gets the content of the first dozen items, then it gets the total number of items. It’s that total number of items that causes the performance problem.

That SQL_CALC_FOUND_ROWS database extension is notoriously slow when the total number of items retrieved by the query is large. Its point is to ignore any LIMIT clause in the query and count the result set after it’s done fetching a few items. That is, sites with thousands of products or posts run slowly for users because they have to show how many total items are available. These archive pages are used to show product catalogs and lists of articles.

The extension also happens to be “deprecated” by the MySQL team at Oracle Corporation. They’ve been saying for years (as of late 2023) that they’re going to get rid of it any day now. Of course they haven’t gotten rid of it. If they do get rid of it before WordPress does, the entire WordPress installed base will migrate to MariaDB.

For better or worse, in the WordPress world for now we’re stuck with using the SQL_CALC_FOUND_ROWS extension. We need the total number of items available to render the pagination UI, even though we’re showing just a few of them per page. The question for now is how to mitigate the slowness of the extension, not how to stop using it.

WordPress plugin

This article presents a WordPress extension that uses the persistent object cache to memoize the results of the row count operations.

Here’s the source code of the plugin. You can install a downloadable and installable .zip file here.

As of late October 2023 it’s very simple. It has no configuration options. And, as of October 2023 it isn’t in the plugin repository yet.

The way this works:

• the first time WordPress runs a particular main loop query to render an archive page, we can capture the total number of rows computed by SQL_CALC_FOUND_ROWS. We store that number in the persistent object cache for later use.
• Thereafter, when we get the same query for the next page, or some other page, in the particular archive’s pagination UI we can re-use that cached total number of rows rather than asking MySQL to compute it again.
• The use of the persistent object cache means all users get the benefit of the cache.
• So, the effect of this extension is to reduce the number of SQL_CALC_FOUND_ROWS to a very low number per page view. This reduces database query time on a busy site.

This all gets done with four filter handlers and some object cache entries.

We hook these filters.

• ‘posts_request’
• ‘found_posts_query’
• ‘found_posts’
• ‘query’

This cache complements the query cache built in to WordPress. And it uses the same techniques as WordPress core to keep track of each separate query pattern.