Faster Archive Pages
Archive pages are any page that lists other items: your blog, the shop page, product category and attribute pages, author archives and custom post type lists. WordPress builds each one with WP_Query, and on a large site the SQL it generates often reads, joins, sorts and counts far more rows than the 20 items the visitor actually sees. That is why archive pages are usually the first pages to slow down as a site grows.
Scalability Pro speeds them up in three layers. Work through them in this order.
1. Create the indexes
The Indexes tab adds indexes the archive queries need, so MySQL can find matching items without scanning whole tables. This changes no behaviour and is often enough on its own. Open the Indexes tab, click Select All, then create or update the indexes.
2. Turn on the safe query tweaks
Two options on the Query Speed tab return exactly the same results, just faster:
- Remove CAST on wp_postmeta removes a redundant
CASTthat stops MySQL using indexes on meta values. - Optimise WooCommerce Group By removes an unnecessary
GROUP BY, or replaces it withDISTINCTwhere joins need it.
Advanced Archive Query Tweaks covers both in detail.
3. Consider the options that change behaviour
These can make a large archive much faster, but each one changes what the visitor sees or how the query behaves. Read the on-screen description, and test on staging first.
- Remove SQL_CALC_ROWS skips counting every matching row, so the page total is no longer exact. See Remove SQL_CALC_FOUND_ROWS for the pagination choices.
- Remove sort options uses the natural database order instead of your default sort. Any
?orderbyin the URL bypasses it. - Remove OR check for private items on front end queries published items only, so private items do not show on the front end.
- Alter main query to use EXISTS rather than LEFT JOIN, on the Advanced tab, rewrites supported taxonomy clauses. See Alter the main query to use EXISTS rather than LEFT JOIN for what it changes and what to test.
To apply any of these to one part of a page rather than the whole site, use Custom Query Alterations.
Measure before and after each step rather than enabling everything at once. If a step makes no difference on your site, leave it off. Measuring the Performance Win shows how.
Very large archives
On large WooCommerce stores, Archives serves the shop, category, tag, attribute and author archives, and custom-field filtered listings, from a pre-built listing table, at every sort whose columns it carries. Enable good settings sets it up from Performance Analysis measurements.
Comments