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.
SQL_CALC_FOUND_ROWS is one of the slowest things a large WooCommerce shop does, and it runs on every archive page load. It’s often the first line to jump out in the slow query log.
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.
The only user-visible change is the final page number. Sequential paging (Next, and the numbered links up to the current page) keeps working exactly as before. If a precise total matters somewhere specific, such as a “showing X of Y results” label, check that spot after enabling the option.
- Large
/shop/pages and custom post type archives (tens of thousands of items or more) - Faceted or filtered archives where every page load runs the count
- Any archive that shows up near the top of your slow query log
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.
Comments