Remove SQL_CALC_FOUND_ROWS

When WordPress runs the main query for an archive page, it doesn’t just fetch the 10 or 20 posts you’re about to display. It also adds SQL_CALC_FOUND_ROWS to the query, which asks MySQL to count every single matching row so WordPress can build the pagination and tell you there are, say, 4,213 products across 211 pages.

On a small archive that’s harmless. On a large one it’s ruinous. If your /shop/ page or a custom post type archive has 100,000 or more matching items, SQL_CALC_FOUND_ROWS forces MySQL to examine every one of those matching rows just to produce a total, even though only a handful are actually shown. This is the single biggest cause of slow archive and shop pages we see, and the cost grows with your catalogue.

The fix is in the Query Speed tab. Enable "Remove SQL_CALC_ROWS" and WordPress stops asking for that full count. The query then does what you’d expect: it finds the rows for the current page and returns them, without walking the entire result set first.

There’s one deliberate trade-off. Because the exact total is no longer calculated, WordPress can’t display a precise final page number. To handle this cleanly, the option lets you replace that final page number with a word of your choosing, "Many" by default. So instead of "Page 1 of 211" your pagination reads "Page 1 of Many", and visitors can still page forwards through the results.

This does change your pagination behaviour on purpose, so it’s worth reviewing how your archives read afterwards. For most large sites, losing an exact page count is a small price for pages that load in a fraction of the time.

In short: SQL_CALC_FOUND_ROWS makes MySQL count your whole archive on every page load. Remove it in the Query Speed tab, optionally swap the final page number for a word, and large archives get much faster.