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.

4 thoughts on “SQL_CALC_FOUND_ROWS in WordPress”

  1. Any particular reason why WordPress by default does not persistence cache the found_posts result. any discussion you came across on trac?

    • I didn’t find anything about this in Trac. I suspect they don’t do this in core because the cost of SQL_CALC_FOUND_ROWS is small except on really large sites that also have complex category arrangments. And because persistent object caches are relatively rare. Core does cache WP_Query results for sites like this, so this plugin helps when site visitors use the pagination features, but not for just rerendering the first page of results.

      Because of the Index WP MySQL For Speed plugin, I sometimes hear from people with large sites. This plugin is an attempt to optimize a site like that.

  2. Hi, thanks for you work with WP performance!!!

    I also often see “SQL_CALC_FOUND_ROWS”….

    Current version ready for use on real sites?

    Interested what next options you want add to plugin?

    • It’s really simple and I’ve been using it on several sites for a couple of weeks now with good results.

      That being said, it hasn’t been reviewed by the WordPress.org plugin review team (yet). So you’ll have to take my word for it when I say it’s safe to use, or inspect its short source code yourself.

      I’d be grateful if you gave it a try. Thanks for your interest! (Don’t forget, it needs a persistent object cache to do anything useful.)


Leave a Comment