Archives: Faster Shop and Category Listings
Every WooCommerce shop page, product category, tag, attribute and author archive asks the database the same two questions: which products belong on this page, in this order, and how many are there in total? WordPress answers by joining wp_posts to wp_term_relationships, wp_postmeta and WooCommerce’s product lookup table, then sorting the whole result to hand back 12 or 24 rows. On a catalogue of a few thousand products that is quick. On hundreds of thousands it is the slowest part of the page, and sorting by price, popularity or rating is the worst of it, because the sort column sits in a different table from the category it is filtered by.
Archives is Scalability Pro’s listing table. It keeps one flattened, indexed table holding, for every product and post, the terms it is filed under (parent categories included), and a copy of the columns your shop sorts and filters on. A listing page reads its rows and its order from one index on that table instead of joining four tables and sorting them.
Available since 6.47.0.
- WooCommerce stores with tens of thousands of products or more, where shop and category pages are slow uncached
- Shops whose visitors sort by price, popularity or rating
- Shops filtered by a custom field, such as stock quantity, through Super Speedy Filters or your own
meta_query - Logged-in customers, who never get a cached page
What is in the listing table
The table is wp_sstr_object_ancestors (your prefix may differ from wp_). It holds:
- One row per post per term, including every ancestor term, so a product in Dresses under Clothing is found by either category without walking the term tree.
- One catalogue row per post, which is what serves the WooCommerce shop page itself, where there is no category to pin.
- Copies of the sort columns:
post_date,post_modifiedandmenu_orderalways,post_titlewhen it is configured. - WooCommerce lookup columns when they are configured:
min_price,max_price,total_sales,average_ratingandrating_count, the columns WooCommerce’s price, popularity and rating sorts read. - Stock and catalogue visibility flags, so “Hide out of stock items from the catalog” and hidden products are applied from the same index.
- Typed custom-field columns for each custom field you add, with a column type chosen to match how the field is compared.
Alongside it sits a small totals table, wp_sstr_listing_counts, with one row per category rather than per product. It answers “Showing 1-24 of 48,210 results” without counting the listing on every page view. See Stored Listing Totals for how those totals work.
Nothing in the table is ever written back to your products. It is a copy, built from your data and kept in step with it.
Which pages it speeds up
Archives serves front-end queries only, never wp-admin. It takes a query when the listing table can return exactly what WordPress would:
- the WooCommerce shop page, at every catalogue sort whose columns are in the table;
- product category, tag and attribute archives, and archives of any other taxonomy;
- author archives;
- listings filtered or sorted by a custom field that has a column in the table;
- filtered shop pages from Super Speedy Filters, when Archives can answer the filtered query.
It serves anonymous visitors and logged-in customers, shop managers and administrators alike, so the listing is fast for the people who never see a cached page.
These are the listing pages measured on a 1,000,000-product WooCommerce store on 13 November 2026, median server page-generation time, with Scalability Pro 6.45.1 configured only by Enable good settings (its indexes, its settings and Archives together):
| Page | WordPress default | Scalability Pro 6.45.1 |
|---|---|---|
| Shop sorted by price | 4,227 ms | 290 ms |
| Shop sorted by price, high to low | 4,405 ms | 282 ms |
| Shop sorted by popularity | 4,098 ms | 257 ms |
| Shop sorted by rating | 4,311 ms | 253 ms |
| First shop page | 937 ms | 297 ms |
| Shop page 50 | 973 ms | 324 ms |
| Shop, logged-in customer | 1,959 ms | 271 ms |
| Product category | 754 ms | 282 ms |
| Stock custom-field filter | 1,707 ms | 441 ms |
Every listing page in that run returned the same products, in the same order, with the same totals as WordPress default. The server was a 4 vCPU, 7.6 GB Hetzner box running WordPress 7.1.2, WooCommerce 11.1.2, MariaDB 10.11.17 and PHP 8.2.34. The stock filter figure depends on Performance Analysis having measured that filtered page before good settings ran, because that is how the stock field was chosen.
For the same 1,000,000-product store measured against another database plugin, see Index WP MySQL For Speed vs Scalability Pro: the shop sorted by price took 4,210 ms with Index WP MySQL For Speed and 290 ms with Scalability Pro, with the same products returned.
Archives does not speed up everything. In the same run these pages were no faster: a sale-price custom-field filter (a field holding decimals that the page compares as whole numbers, which Archives declines), price-range filters, product searches on the front end and in wp-admin, and the wp-admin products list. Search belongs to Super Speedy Search.
Setting it up with Enable good settings
The quickest route is Enable good settings on the Main tab. Archives is configured from measurement, not guesswork, so this part of the run needs the free Super Speedy Performance Analysis plugin, active and with its profiler on. When it cannot measure yet, the Main tab shows a single button to install it, activate it or enable its profiler. Without Performance Analysis the popover has no Archives row, good settings leaves Archives alone, and you configure it by hand on the Archives tab instead.
With Performance Analysis ready, the popover runs these Archives steps around the indexes and settings:
- Turn the archive optimisation off first, so the baseline measures your real queries rather than ones Archives has rewritten.
- Profile every key page with Performance Analysis, as for the rest of good settings.
- After the indexes and settings, Configure, build and switch on the archive index:
- it profiles your sorted shop pages: the shop and its biggest product category, once for each sort your shop offers other than the default, up to 10 sorted pages, then one filtered page for each Super Speedy Filters custom-field filter, up to 20 pages in all;
- it plans the table from all of those measurements together, and adds the price, popularity and rating lookup columns for every one of those sorts the shop offers, whether or not a page was measured with it;
- it refuses a configuration known to make category pages slower, and stops with the reason instead of applying it;
- it builds the table and switches the optimisation on.
- Profile the same pages again and compare.
Profiling runs as loopback requests from your server to itself. If HTTP basic auth, a firewall or a CDN challenge blocks those, the Archives row fails with a message saying so while the rest of good settings completes. Allow loopback requests and run good settings again.
How custom fields are chosen
Custom fields are the one part of the table that differs from shop to shop, so good settings looks for the ones your shop really filters or sorts on. It gathers candidates from two places: the custom fields that Performance Analysis recorded being filtered or sorted on in any run of the last 30 days (plus this run’s own pages), and the custom-field filters configured in Super Speedy Filters.
A field is added when all of these hold:
- a page filtering or sorting on it took 50 ms or more for its main query, measured with Archives off;
- every stored value of the field fits one column type: whole numbers become
intorbigint, numbers with up to 2 decimal places becomedecimal, and short text (up to 200 characters) becomesvarchar; - no post holds more than one value for it, and no value is NULL;
- it is not
_stock_status, which has its own stock columns in the table.
At most 3 fields are added in one run, sorts first, then the slowest pages. The popover and wp spro recommended list every field considered and why it was or wasn’t added, for example “Custom field _stock: added as int (…)” or “not added, it holds decimals but is compared as a whole number (NUMERIC), which WordPress truncates”.
A field must have been measured to be added. If you filter on a custom field that no Performance Analysis run has profiled, it is listed as “no page using it was measured”. Profile that filtered page in Performance Analysis, then run good settings again.
The Archives tab
The Archives tab is the last tab in Super Speedy > Scalability Pro. Everything good settings does can be done or adjusted here.
Enable flattened term relationships mirror table holds the controls you use most:
- Optimisation Status – Enabled or Disabled. It can only be switched on once the table exists (“Build the table first to enable the optimisation.”). Click Save Settings after changing it.
- Replace Super Speedy Filters object_ancestors table – off by default. When on, Super Speedy Filters reads the Archives table instead of building and maintaining its own. Leave it off unless you have configured Archives for that site and want one table instead of two.
- Build Status – Table Status (Built or Not Built), Object Count and Last Built.
- Build Table (or Rebuild Table once it exists), Resume Build while a build is part-way through, and Delete Table.
Measure Impact runs Performance Analysis over the same pages twice, first with the optimisation off and then on, without changing the configuration or rebuilding the table. It needs the table built and no build running.
WP-CLI shows wp ssa build with a Copy button, plus wp ssa status, wp ssa enable, wp ssa disable and wp ssa configure.
Post Columns chooses which of post_date, post_modified, menu_order and post_title the table carries for sorting. post_title stores the first 255 characters of every title, plus two more indexes, on every row, so only tick it when your shop sorts by name.
Meta Ordering / Filtering Keys lists the custom fields in the table, each with a column type (varchar, bigint, decimal, float, int, datetime, date or bit). Use + Add Meta Key to add one by hand, for example _stock as int.
WooCommerce Lookup Columns copies min_price, max_price, total_sales, average_rating and rating_count from WooCommerce’s lookup table, which is what lets the price, popularity and rating sorts be served from Archives.
Changing columns, custom fields or lookup columns on this tab and expecting the shop to change straight away. Every one of those changes needs a rebuild: save, then click Rebuild Table (or run wp ssa build).
Building the table, and what visitors see meanwhile
The build reads your posts, term relationships, custom fields and WooCommerce’s lookup table, and writes only to a temporary table of its own. When every step has finished, the finished table replaces the live one in a single atomic rename. So during a build:
- your shop keeps working; it uses the previous table, or WordPress’s own queries if there was no table before;
- no request ever finds the table missing part-way through;
- a step that fails stops the build, shows the database error and resumes from that step when you press the button again, with the previous table still in use.
Build time grows with the size of the catalogue and the number of columns, so on a large catalogue expect it to take a while. A build started from the Archives tab is driven by that browser tab: if the tab is closed or the connection drops, open the Archives tab again and click Resume Build. On a large site, wp ssa build over SSH is the more comfortable way to run it, because it needs no browser and runs every step in one go. Both finish by switching the optimisation on.
On a large catalogue, run the first build with wp ssa build at a quiet time, and let any index build on the Indexes tab finish first. Both read wp_posts and wp_postmeta heavily.
How it stays in sync
Once built and switched on, the table is kept in step with your shop in the same request that changes it. The rows for a post are refreshed when it is:
- created, saved, published, trashed, restored or deleted, through the editor, quick edit, bulk edit or the REST API;
- given or removed from a category, tag or attribute, or when a term itself changes;
- changed through WooCommerce: price, stock status, sales count, a variation, or a product duplicated;
- imported by an importer that uses WordPress or WooCommerce functions, such as WP All Import;
- changed in a configured custom field, even when the product itself is not saved.
The stored totals move with those rows, so the product count on a listing stays exact.
A tool that writes straight into wp_posts, wp_postmeta or wc_product_meta_lookup with its own SQL bypasses all of that, so Archives never hears about the change. After a direct database write, run a rebuild.
When Archives steps aside
Archives only takes a query it can answer exactly as WordPress would. Anything else runs WordPress’s own SQL, unchanged:
- wp-admin, and any query while the optimisation is off or the table does not exist;
- a sort with no column in the table, such as a custom field that is not configured, or a price sort without the price lookup columns;
- WooCommerce’s price filter (
min_priceormax_pricein the URL); - Super Speedy Filters’ on-sale and variation filters;
- a custom-field filter whose comparison would give different results on the typed column, such as a field holding decimals compared as a whole number, or an OR between custom-field clauses.
Those pages show exactly the same products as they would without Archives; they simply don’t get the speed-up. If a page you expected to be fast is not, the reason is usually in that list.
Switching it off
- Archives tab: set Optimisation Status to Disabled and click Save Settings. The table stays in place, and every query goes back to WordPress’s own SQL.
- WP-CLI:
wp ssa disable, andwp ssa enableto switch it back on. - Emergency: add
define( 'SPRO_DISABLE_ARCHIVES', true );towp-config.php. The Archives code does not load at all: no tab, nowp ssacommands, no query changes. Use it if wp-admin will not load and you suspect Archives.
While the optimisation is off, nothing keeps the table up to date. Rebuild it before switching it back on.
Deactivating, clearing and deleting
- Deactivating Scalability Pro switches the Archives optimisation off and keeps the table. Because nothing maintains the table while the plugin is off, reactivating shows a notice with a link to rebuild it and switch it back on.
- Clear all options on the Main tab resets Scalability Pro’s own settings and indexes, and leaves the Archives table and its settings alone, so a reset does not cost you a rebuild.
- Deleting Scalability Pro removes the Archives table and its settings along with the rest of the plugin’s data. See Uninstalling Scalability Pro Safely.
Super Speedy Archives as a separate plugin
Archives was once sold as a separate add-on, Super Speedy Archives. It is part of Scalability Pro, with no separate download or licence. If the old plugin is installed, Scalability Pro deactivates it the first time it loads beside it and shows a one-time notice. Your Archives settings and table are kept and used as they are, and you can delete the old plugin.
WP-CLI and AI agents
| Command | What it does |
|---|---|
wp ssa build | Full rebuild, then switches the optimisation on. wp ssa rebuild is the same |
wp ssa status | Build state, optimisation state and row count |
wp ssa enable / wp ssa disable | Switch the optimisation on or off |
wp ssa configure | Set columns and custom fields, for example --add_meta_key=_stock:int |
wp ssa configure-from-analysis | Plan from the latest Performance Analysis analysis plus every page analysed since, or from --runs=<ids> |
wp spro recommended | Run the whole of Enable good settings, Archives included |
Over MCP, the super-speedy-archives abilities (get-status, describe-config, get-config, plan-from-analysis, apply-from-analysis, start-build and delete-table) let an AI agent inspect, plan and build the table. See Controlling Scalability Pro with MCP.
Troubleshooting
The Archives row is missing from the Enable good settings popover. Performance Analysis is not active, or SPRO_DISABLE_ARCHIVES is set. Install and activate Super Speedy Performance Analysis from the Main tab, or configure Archives by hand on the Archives tab.
Good settings stops with “This site blocks loopback requests”. Performance Analysis profiles pages by requesting them from the server. Allow loopback requests through your basic auth, firewall or CDN, then run good settings again.
“Another Archives build is already running.” Wait for it to finish (the Archives tab shows its progress), then run good settings again.
A build stopped with a database error. Fix the cause it names (often disk space or a lock timeout), then click Resume Build. Your shop keeps using the previous table meanwhile.
A sorted or filtered page is no faster. Check the list under “When Archives steps aside”. The usual causes are a sort with no column in the table, a custom field that was not added (the popover says why), or a price-range filter.
A product count or listing looks out of date. Something changed the database without going through WordPress or WooCommerce. Save the product, or rebuild the table.
Comments