Features

Stored Listing Totals

Published · Last updated

Available since 6.47.0.

Every WooCommerce shop page shows a product total: “Showing 1–12 of 48,210 results”, followed by the page numbers. To print that total, WordPress normally asks the database to count every product that matches the page, on every page view, even though the visitor only sees 12 of them. The count grows with the catalogue, so on a large shop it costs far more than fetching the page itself.

Scalability Pro keeps those totals in a small table instead. The shop and each product category read their total from one row, and the page query fetches only the products on that page.

What is counted

The totals table, wp_sstr_listing_counts, is built alongside the Archives listing table. It holds one row per listing:

  • The whole shop. Every published product in the catalogue.
  • Each product category. Its products and those of its subcategories, matching WooCommerce’s own category pages.

Each row is split by post type, post status and catalogue visibility, and stores two numbers: how many products the listing holds, and how many of those are in stock. With Hide out of stock items from the catalog switched on in WooCommerce, the in-stock figure is the one shown. The table stays small: one row per category, not per product. A store with a million products and around a hundred categories has a table of about 150 rows.

Why this matters

The total is the expensive part of a large shop page, not the products. Fetching 12 products in price order reads 12 index entries; counting the listing reads all of them.

How the totals stay exact

The totals are kept in step with the products, not recalculated on a schedule:

  • Saving, publishing, trashing or deleting a product subtracts its old rows from the totals and adds its new ones in the same update.
  • Hiding a product from the catalogue, or changing its stock status through WooCommerce (the product editor, quick edit, bulk edit, the REST API, or an importer that uses WooCommerce’s product API) moves it between the visible and in-stock figures the same way.
  • A full rebuild of the Archives table recalculates every total from scratch and swaps the new table in together with the rebuilt Archives table.

So the total on a page matches the count WordPress would produce.

Common mistake

A tool that writes _stock_status straight into the database, bypassing WooCommerce, changes the stock figure WordPress sees without telling Scalability Pro. Save the product once, or run a rebuild from the Archives tab, to bring the total back in step.

When the stored total is not used

A stored total is only used when the page asks exactly the question the table answers: one post type, one status, the whole shop or a single category, plus the catalogue and stock visibility rules. Anything else counts the matching rows as WordPress normally does:

  • an attribute, price or other filter applied to the listing;
  • a logged-in editor whose listing includes private or draft products;
  • a query limited to a hand-picked list of products.

Those pages show an exact total too; they simply pay for the count.

Did you know?

With Remove SQL_CALC_FOUND_ROWS switched on, a listing that has a stored total shows the exact figure rather than an estimate. Listings without one keep the estimate or pager style you chose.

Turning it on

There is no separate switch. The totals table is part of the Archives table, so Enable good settings creates it when it builds Archives, as does Build Table or Rebuild Table on the Archives tab, or wp ssa build. The totals are used while the Archives optimisation is switched on. Building it adds little to a full Archives build: it is one grouped pass over the finished table.

Further reading

the listing table the shop and category pages are served from
how pagination works on listings without a stored total
compare a shop page before and after

Comments