The Scalability Pro Plugin is the core WP Intense plugin to transform the speed and scalability of your WordPress site. It does so by eliminating table scans.
- It adds 13 indexes to your key WordPress tables to help optimise underlying SQL queries
- It alters database queries to make them more efficient, avoiding table scans
- It provides options to disable, alter or microcache slow WordPress/WooCommerce/Import functionality
A note about Table Scans and Page Caching
WordPress has many occasions where table scans occur against the database. For example, the Price Filter causes a table scan to obtain the maximum and minimum price range and means your page is reading every item in your database to gather this information. When you only have a few hundred products in your WooCommerce store, you would never notice. But once you get to a few thousand or tens or hundreds of thousands this becomes dreadful for your server.
Page Caching helps – especially if you can implement it at the Nginx or Varnish level. But still, when you have a huge site, you cannot cache everything, so your site still needs to be fast for newly generated pages.
This is why eliminating Table Scans is fundamental to solving your WordPress scalability issues. Instead of reading every item in the database, well written queries read only the 20 items they need to read in order to present 20 items on page.
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.
Scalability Pro speeds up your imports
Plugins like Datafeedr and WP All Import store information in wp_postmeta about imports and the SQL queries they use cause table scans to happen. When they are importing, they will check wp_postmeta for certain values (meta_value column on the table). By default, WordPress has no index on this table – probably because meta_value is a ‘text’ field meaning unknown size. You can still create an index on the first few characters however and massively speed up the majority of checks against this table.
Scalability Pro also provides an option to let you Defer Term Counts until 2AM. Term Counts involve recounting the number of items (posts, products, custom post types) within a category or taxonomy. When you are importing, this can mean term counts after every individual item. If you are importing 10,000 products then by deferring the term counts, you can run the count once when the import is finished rather than 10,000 times.
If you’ve been experiencing your imports becoming slower the bigger your database gets, Scalability Pro will help you.
Scalability Pro speeds up wp-admin and your site in general
The 13 indexes speed up multiple queries around your site including wp-admin.
Additional options are available to speed up wp-admin archive pages (e.g. post lists or product lists) and editing pages.
Scalability Pro Installation & Options
Go to Plugins->Add New and browse to your zip file for Scalability Pro. Upload then activate.
After you have installed and activated Scalability Pro, you should visit the Settings->Scalability Pro page.
Click the big button on the settings page to create your indexes. This is a one-time operation and the indexes will remain in place even after you de-activate the plugin. If you wish to remove the indexes before de-activation, you should click the button to delete the indexes.
On larger sites creating these indexes may take some time but you can leave the page and come back later if you need to.
Below the list of indexes you’ll find the various options for Scalability Pro.
Scalability Pro Options
The options below involve either removing, altering or microcaching certain slow parts of WordPress.
We have tried to group these as best as possible, and we have enabled some key defaults. Please read the description next to each item carefully so you know if you should activate it or not. These options are ‘safe’ in that when you change the option back, your site will be as it was before.
Each option tells you which page types to check for before & after performance. You can read more about each option below, and see Dave using the various options on our demo site with one million products in the video below.
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.
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.
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.
Faster Product Detail or Single Post Pages
If your product detail or single pages include widgets which run SQL which is optimised by the ‘archive’ optimisations above, then these pages will subequently be faster as a result.
There is also this option, specifically for WooCommerce Product Variations
Remove WooCommerce Ajax Variations calculations
If you have products with LOTS of variations, WooCommerce runs some slow code to count the variations on the product detail page. By disabling this, it forces some Ajax code to run when the user clicks the variation option which is far faster.
Faster Imports – Optimising wp_postmeta
Defer term counting
Helps optimise imports and other bulk modifications by deferring term counts (recounting items per category) to a nightly job. If you are importing 10,000 products, this changes 10,000 time-intensive recounts into 1 recount.
Specific WooCommerce Optimisations
The options above all help WooCommerce, but if you have a large site or a large number of orders, then these specific WooCommerce options will help you.
Optimising WooCommerce Updates
WooCommerce has code on multiple pages which forces deletes against wp_options in relation to WooCommerce version upgrades. These delete operations are written in such a way that they cannot use indexes on wp_options. This means, if you have a lot of options (e.g. a lot of transients) that your wp-admin pages will intermittently be locked – on our reference site (820,000+ products) this intermittent shutout can last for up to 3 minutes. This in turn means these ‘updates’ never truly complete.
Enabling this option makes Scalability Pro rewrite these delete operations to be able to use the indexes and makes the delete operation virtually instant. This option is enabled by default.
Remove WooCommerce Order Summary
If you have a lot of orders, you will notice your wp-admin pages slowing down. WooCommerce runs the Order Summary dashboard script which you probably never look at or use and it can add multiple seconds to wp-admin page load, depending on your order-volume.
Order summaries are still available by going into WooCommerce -> Reports, this option just removes the dashboard widget and is enabled by default.
Faster WP-Admin Page, Post & Product Archives
Remove cast on postmeta queries
There is a CAST function applied to the ‘value’ column on many wp_postmeta queries. This cast is redundant since MySQL auto-casts where necessary, but worse, because a function is applied, mysql cannot use any indexes we create on these columns. Highly recommend you remove it.
Remove post-type category drop down
If you have a lot of items (posts, products, whatever) of one type, your wp-admin archive will be slow.
There are 3 causes – post_type dropdown, post_status count and post category dropdown.
This option lets you optimise all 3 of these dropdowns so that your archive pages will be lightning fast. You can either remove the functionality or microcache it. If cached, the dropdowns will regenerate nightly.
Faster WP-Admin Page, Post & Product Editing
Remove custom-meta select box
Page and post editing pages in wp-admin include a really badly written sql query to populate the ‘custom meta’ select box. This is redundant because if you want to edit meta, you can type in the meta name. Highly recommended you remove it.
How to tell if Scalability Pro will help your specific scalability issue
There are 3 common sources of Scalability issues in WordPress.
The first source of scalability issues is if you have a plugin (or multiple plugins) which is causing too many SQL queries to occur.
In this case, Scalability Pro has less impact and the better solution is to figure out which plugin(s) are causing too many queries to occur and replace them with alternative plugins or just remove them.
The second is that SQL queries are taking too long to return. In this case, Scalability Pro will definitely help transform the speed and scalability of your site.
The third is if you have a slow server or server-stack (you should aim for SSD, Nginx, PHP7 and PerconaDB or MariaDB as a minimum)
To check if you have too many queries or if you have slow queries, install the Query Monitor plugin and visit your slow URLs. Look at the top Query Monitor bar and look for the 4 values it provides:
Using the example above, the figures are:
- Total time including PHP and MySQL: 2.92 seconds
- Page size: 9.95MB used to generate this page
- Total MySQL time: 2.36 seconds of DB time (that’s a LOT!)
- Total MySQL queries: 54 queries (that’s not a lot)
With the example above, there are not too many queries – 54 is not a lot for WordPress. If you have more than 400 queries you should use Query Monitor to figure out the culprit plugin.
How to fix large numbers of queries
If you have a high number of queries, you should click the Query Monitor bar and ‘View Queries by Component’. Query Monitor will give you a rundown of which plugins are causing these queries.
Note: If you have a large count in one plugin (e.g. WooCommerce) it can frequently be caused by plugins or widgets for WooCommerce since those plugins or widgets will hook into WooCommerce ‘actions’ making it difficult for Query Monitor to tell you the real source of the query.
You can drill down into some queries and see which files were used to generate this query. By tracing backwards, you can find the problem plugin.
Another way is simply to disable half your plugins, check if the query count has dropped, if it has, re-enable half of those plugins until you narrow down to the problem.
If you have a low query count and your slow page speed is caused by slow queries, install and activate Scalability Pro and let it solve these issues for you.
Consider replacing your WooCommerce widgets and WooCommerce shortcodes
The default WooCommerce shortcodes cause hundreds or thousands of queries to occur as well as table scans – this will kill performance even with Scalability Pro installed.
The default WooCommerce Price Filter causes table scans – this will also kill performance even with Scalability Pro installed.
If you’re looking for replacement filters, check out our Faster Woo Widgets plugin – you can get Scalability Pro and the Faster Woo Widgets plugin as part of our Performance Plugin Pack.
Scalability Pro works perfectly in harmony with all caching plugins and you should not consider Scalability Pro to be a replacement for a caching plugin. It optimises uncached performance, not cached performance. Therefore, you should get W3 Total Cache or WP Rocket or a similar page caching plugin.
Scalability Pro also works brilliantly with other performance-enhancing plugins from WP Intense:
- Faster Woo Widgets – replacement filters and widgets that are far faster than the vanilla WooCommerce widgets
- Auto Infinite Scroll – there is an advanced option in Scalability Pro to remove pagination. If you do this, you need an infinite scroll plugin. Our Auto Infinite Scroll is highly performant, comes pre-configured for WooCommerce and has the added benefit of caching scrolled archive pages in the local browser storage system. That means, when a user visits a specific product page and then clicks back, your server does not need to refresh the content and the user will return to the exact spot in their scroll down your archive.
- Super Speedy Search – if you need free text search for WooCommerce, Super Speedy Search works out the box. It optimises the default WooCommerce free-text search, but it also provides an ajax free-text widget which updates the product archive as the user types.
Scalability Pro Demo
You can see the speed of a live site using this here: http://www.homes-abroad.net/ and a demo/dev site here (with 817,000 products, but dev, so not guaranteed up all the time): https://www.foundthru.com/shop/