Description
The Scalability Pro Plugin is the core WP Intense plugin to transform the speed and scalability of your WordPress site. It does so primarily by eliminating table scans and using index seeks instead.
A highly scalable site will not slow down as your site gets bigger. Without Scalability Pro, you’ll likely see a degredation of performance on your website once you exceed 5000 products, orders or users. Caching can help, but what you really need is to make sure your website scales, regardless of how big your website becomes. A scalable website is as perceptibly fast at generating a web page when there are 1,000,000 users as it is when there are 10,000.
How it works
- Makes WP_Query lightning fast (0.4 seconds uncached vs over 180 seconds for 820,000 products on /shop/ page)
- Works by optimising your underlying database and queries and eliminating table scans
- Provides options to alter or remove slow WooCommerce and WordPress features
- Complements caching systems perfectly
- Speeds up SLOW queries – install the Query Monitor plugin and view your slowest pages, if you see slow queries being reported, Scalability Pro will help. If you instead see thousands of queries occurring, there is a separate solution for that type of performance problem.
- It adds 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
If Scalability Pro is implemented and there are still table scans occurring, we will fix them.
Three-pronged approach to real scalability
Scalability Pro works best in conjunction with Redis object caching and a good quality hosting stack. Any one of these on their own is not enough. If too many queries (over 1000) are being registered in Query Monitor when you have Redis activated, then the related plugin is not using an object cache. If this is the case, you should contact the plugin author and tell us too. We can advise them on how to fix their plugin to scale properly with good use of the object cache. If you’re still seeing slow queries, as opposed to too many queries, then Scalability Pro has missed something — please let us know and we’ll endeavour to get it fixed.
Five problem areas for Scalability
Slow queries as your site gets bigger – this is what Scalability Pro helps with. It makes slow queries run quickly when your site is bigger.
Too many queries – the Redis object cache helps with this, unless the plugin author has not implemented object-caching support
Too much RAM and CPU consumption in PHP – you’ll see low SQL time, low numbers of queries, but long page generation time and large RAM usage. You’ll probably find some queries that return large row counts, often over 5000 rows returned or more. These plugins are filling objects with those 5000 rows every page load. Normally, this can be fixed by implementing an object cache – but it depends on the type of data.
Slow API calls – your site may be using ajax, or it may be calling third-party APIs. This is especially true when an admin is logged in, and especially so on the dashboard and the plugins page and especially so if you have unlicensed plugins that keep trying to update themselves.
Poor hosting – make sure you get a good quality hosting stack. PHP 7, MySQL 8, nginx, fastcgi_cache or some kind of http accelerator, redis in-memory-only and a proper configuration.
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.
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.
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.
The 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/
Rik E. (verified owner) –
Today put this tool on my website with 50,000+ products. The page loading times were really 10 to 20 seconds during imports via wpallimport. Set all steps and go through and now around 1 to 2.
What a great tool 🙂
Dave H. –
Thank you! This plugin was built for large stores or large blogs, so it’s really great to hear from happy customers.
Zaid I. (verified owner) –
BAD WEBSITE, BAD SUPPORT, BAD PLUGINS
I purchased the plugin, then I’ve installed it on my website and it did not do anything, literally nothing.
I’ve tried to contact the support and I did not receive any reply, Also I’ve tried to request a refund and no one contacted me.
This is a lie, be careful. DON’T DEAL WITH THIS WEBSITE.
Dave H. –
I’m never happy to hear about unhappy customers – I’m really sorry it didn’t speed things up for you.
Here’s a guide explaining what each of the plugins do:
https://www.wpintense.com/2018/06/30/wordpress-performance-quick-start-guide/
As for contacting us – please use the Q&A through the site and you will get a response – I don’t see any questions from you. I do not reply to email questions any more if that’s where you tried to contact me. This was an intentional decision to eliminate the vast number of questions I used to answer in private – now other customers can benefit from the questions and answers.
You can see my blog article discussing this here:
https://www.wpintense.com/2020/12/07/december-development-update/
If you are really in a rush, you can contact me through facebook, but if it’s a general question I’ll still ask you to ask the question through our Q&A system.
For refunds – email [email protected]
I just looked through my emails and I see that your site crashed when you were creating the indexes – this is almost certainly due to your server running out of disk space. The indexes don’t take up too much extra space on your disks, but they do need some free space in order to be created.
John Black –
Hello!
I have a wordpress e-commerce website and woocommer. My website has about 100k products x 288 variants. And recently, I often don’t have access to line items. So does your plugin optimize that? Thank you very much!
Dave H. –
Yes, if you install Query Monitor, you’ll see a slow main query – it optimises those primarily. For the variations you should add Redis object cache – see my stack building guide: https://www.wpintense.com/2018/10/20/installing-the-fastest-wordpress-stack-ubuntu-18-mysql-8/
Dave H. –
This is a test question
savvas z. (verified owner) –
I can’t praise enough this plugin. It helped me scale my woocommerce site. Importing products now it’s a breeze! Scalability pro cut the importing time to half (i am also using external images). Also my site loads much much faster than previously! Next stop: super speedy search and faster woo widgets.
If you want to scale your woocommerce site don’t miss Dave’s plugins. They are a must.
Thanks Dave
Joseph M. –
It took me almost a year to find this plugin. I can truthfully say it is without question a must have if you have any intensions of scaling woocommerce. Not to mention awesome support the few issues I had because of my theme were taken care of by Dave himself. Not only did he fix the small issues with my theme he took the time via my request to touch up my database while he was in there. Thank again I am thankful for your help and putting up with my relentless questions. Utmost respect – joe miller
cory d. (verified owner) –
This plugin is brilliant!!! Would give it 10 stars if I could. Brought the site loading times down to less than 2 seconds – was sitting at about 10 seconds before. Had a few problems with the configuration but the support was great and got me setup in no time. I highly recommend this plugin.
Harris –
Works very well, noticeable results immediately.
Paul A. –
My client site has about 10k products variations. Page loading time was around 10s, and by adding this plugin and doing a quick setup by following the guide, the WooCommerce pages now load in 2s. Still a bit more tweaking to do on my side with a few different options, but this plugin took my client site from being useless (yes a 10s load time for product pages renders your site useless!) to very useful 😉
Thanks for the great plugin and support!
Anon –
Thanks Paul. It’s great to hear your feedback and I’m glad the plugin helps a lot. There’s more coming soon to shave more milliseconds off those page load speeds. In particular, I’ll be focusing on WP_Term_Query optimisations which are probably what are taking up the last of your slow load. (I consider 2s to still be slow)
Abhishek J. (verified owner) –
I bought the plugin a month back and I was impressed by Dave’s support, he is really knowledgeable. I had a product database of around 300k products which is also using wp-allimports custom fields functionality which made my postmeta database comprising of about 50 million rows.
Pros:
– This plugin creates indexes and has options to fix edit page issue in admin panel – I received when before, but when I started using scalability pro, it fixed it.
– My page load time for different categories decreased from 30 seconds to around 20 seconds with products around 100k which is great- all thanks to indexes created by the plugin
– Good support from Dave(the plugin was not working on my website, but Dave did do all the work manually on my website and recommended me a lot of stuff)
Cons:
– Scalability Pro is I think at its initial stages and it will improve gradually. Different theme support and good service in future can sustain. The reason I’m listing this is because when I started using this plugin, my product database tripled, but it was fixed with in 2 days by Dave when I told him, so if you’re having high traffic I would recommend to test it on your test site before making it live.
– It won’t necessarily solve all the problems, I still can’t use filters on Products section(where I can see all the products) in wp-admin section, so you will be using it like a usual customer only or if you’re technical you can go to MySQL database, see your products. It should have solved this problem
– Filters provided by scalability pro don’t work, I have raised issue with Dave but not fixed yet. Filters also don’t support wp_postmeta fields or custom fields. I really don’t like to message him for everything, it should have been solved.
– I think search still doesn’t use Match .. Against(default wordpress search) or as a matter of fact on any pages.. I analysed it using query monitor but it still seems to use Like only. No support for custom fields. Other problem is created by wp_postmeta table(as all the extra fields related to product are in wp_postmeta) Wp_posts table is not that big a deal, as only 1 row is created per product while in wp_postmeta around 16-20 rows are created per product, so either you’ll have to write custom search. If custom search was there with this plugin, you could really see performance improvements in your site(I’m not ignoring the performance improvements but they are not noticeable for me as my search dependence is on wp_postmeta table and category page loads under 20 seconds, which should have been under 2 seconds). Dave talked about releasing beta version for custom search but still isn’t released yet.
I am waiting for updates on this plugin and hopefully errors I listed will be solved in future. All in all I would recommend to go with this plugin, it will offer you useful insights about your website and hopefully if you’re a wordress programmer, you can solve those or if you aren’t, you can always hire Dave or other programmer.
This plugin offered me a ray of hope that I don’t have to discard my wordpress website and go with Magento(if you read it on stackoverflow, everyone would recommend agianst wordpress for products with large database). If you’re programmer or your programmer can understand the way plugin works and take it from there instead of relying on Dave, this is great product.
Thanks
Anon –
Hi – thanks for the detailed review.
Regarding missing filters – there was a bug introduced in the last version which caused this error on some setups, to do with adding the manual sorting functionality.
A fix has been coded up and is ready to be deployed, I’m just adding compatibility with the WooBrands plugin before I deploy.
re: Match against search (instead of like) this functionality was removed from Scalability Pro and has been added to a separate plugin called Super Speed Search which everyone who has purchased Scalability Pro to date will get for free. This new plugin is almost ready and is so fast that the search results update using ajax as you type (tested on 800,000+ product reference site).
Sorry about the delay with these releases – I’ve been working on making super speedy search as compatible as possible with as many themes as possible.
Aaron M. (verified owner) –
Our website over at nyde.co.uk was under development and suffering from a complete slowdown. I purchased Scalability Pro in the hope that it would give things a much needed boost, but didn’t expect the results to be quite as brilliant as they were. Dave worked with me to configure the plugin and also identified numerous bottlenecks on my website. The improvement is unbelievable! Really pleased with my purchase and I couldn’t recommend Scalability Pro or working with Dave on performance enhancements enough.
Matt –
Does this plugin work in a WP multisite environment that makes use of the MultiStore plugin?
Will the plugin Ajax Search Pro blend in nicely?
Anon –
Yes it works in multisite but it may not yet optimise everything – I’m working on this.
I’m unsure about Ajax Search Pro – if it uses, presumably it does, the WP_Query object to perform its search, then these will be replaced by Scalability Pro with a full text search which is faster and more accurate.
If any of these things do not work to your satisfaction, I will fix them for you or refund you.
Sam –
Does this plugin also tweak the woocommerce search? or do I need to configure elasticsearch in addition?
Anon –
Yes it alters the WooCommerce text search to use the MATCH operator and a fulltext index rather than the awful default of the LIKE operator.
That means faster and more accurate free-text search results.
Istvan D. (verified owner) –
The Scalability Pro plugin and Dave’s support is simply amazing! I was struggling with 30+ seconds load times on my shop and the plugin alone has reduced this under 3 seconds. Wow! This plugin and generally everything around WPIntense is absolutely recommended!
Erik D. (verified owner) –
I have a site with many products and heard about Scalability Pro and after installing this plugin my site got blazing fast.
I recommend this anytime for everybody who want fast woocommerce site.
Thanks to Dave for support.
Dirk (verified owner) –
I ordered the WPI Performance Plugin and I´m absolutly impressed. I never thought that I can get that speed on my site. I´m planing to build more sites and shure I will buy the WPI Performance Plugin for that sites too.