Scalability Pro speeds up your archive pages by speeding up WP_Query
Archive pages are the pages on your WordPress site that contain lists of other pages. Your blog archive, your /shop/ page, your custom post type archive, your category or author archives. All of these lists become slower to generate once you have more of them.
Scalability is about avoiding slower speeds as data volumes get bigger. By avoiding table scans, it’s possible to have a 1 million product store respond as quickly as a 20 product store.
Faster Archive Pages – Optimising WP_Query
To view your current WP_Query speed, install and activate the free Query Monitor plugin then visit your slowest archive page. Archive pages are any page which list multiple other pages. You have a list of blog articles – that’s an archive page. You might have pages of products, these are archive pages. You might have category pages listing items in that category, any list of posts is an archive page.
Remove Sort Options
This option lets you use the natural index sort order on wp_posts. When WP_Query (used by WooCommerce, Custom Post Types and Blog archives) to fetch data, it provides sorting options and these sorting options require a full sort of the retrieved data. On /shop/ or large top level categories that means a full table or index scan which can take a few seconds and thrash the disk and CPU.
Note: Using the natural sort order will disable any sort options you currently provide for your users and revert to the natural sort order (normally sort by Date DESC). If you choose this route, you should hide those sort options.
Remove Pagination
When WP_Query fetches posts/products, it also calculates the total number of matching products. This is useful to display at the top of your shop/page, e.g. Showing 1 – 50 of 650,000. However, this count requires either an index scan or table scan. Removing it, can result in faster WP_Query speed.
Depending on your setup, you may need to switch to the natural sort order above too for successful use of the indexes.
Note: Infinite scroll is recommended if you remove pagination.
We default to faking there being 100 pages for your archives if you enable this option, but if you wish to change that you can do so by adding something like this code to your functions.php:
add_filter('spro_num_pages', function($num_pages) {
return 30;
}, 10, 1);
Alter main query to use EXISTS rather than LEFT JOIN
WARNING – EXPERIMENTAL! (80% compatibility roughly) This option will attempt to alter the main WP_QUERY SQL call to use WHERE EXISTS rather than a LEFT JOIN.
This means that the SQL Query can avoid using a GROUP BY. This feature also removes SORTING of results.
In many cases it can cause the indexes to be used properly and can avoid table scans. On our reference server, it turns a 4.6 second query (820,000 products) to a 0.05 second query.
It definitely will not alter admin queries otherwise we might accidentally break your wp-admin pages. In future, once it’s proven resilient, it may be used to optimise wp-admin calls too.
Optimise WooCommerce Group By SQL
In some situations, WooCommerce runs a GROUP BY query even when there is no need. That causes either an index scan or a table scan, followed by a sort. If you see compatibility issues, this is one option you might try disabling, otherwise choose to optimise.
