Scalability Pro

Settings and Options Reference

Published · Last updated

Scalability Pro fixes slow SQL and database queries and trims unnecessary WordPress and WooCommerce work, so that any page-caching plugin can populate its cache fast. The goal is a site that stays quick even at very large scale, with 1 million or more products achievable on a $40 per month server. This article is the full walkthrough of the settings screen: it takes each tab in turn, explains what it is for, lists the notable options, and points you to the dedicated article for anything you want in depth.

You will find the settings at Settings > Scalability Pro. The tabs run in this order: Intro, Indexes, Query Speed, Single Pages, Imports, Bloat, WP Admin, Slow Query Log and Advanced. The options are grouped by the problem they solve, so you only need the tabs that match your goal. One distinction runs through the whole screen: indexes and most import options are safe to turn on and simply make things faster, whereas several archive and query options change behaviour as well as speed. Read the on-screen description before enabling anything on the Query Speed tab.

Before you start: create your indexes

The single most important and completely safe step is to create the database indexes. Indexes speed up queries all over your site without changing any behaviour, so there is no downside. Go to the Indexes tab, select all the indexes, and build them. On a large site do this at a quiet time, because building the indexes can take a few minutes and can slow the site down while it runs. For many sites the indexes alone make the biggest difference, and if your slow queries were caused by missing indexes you may not need to change anything else.

Recommended

Create all the indexes before touching any other tab. It is the one change that is pure upside – faster queries, no behaviour change – and it is often enough on its own.

Indexes

The Indexes tab shows a table of every index Scalability Pro can create and maintain, so your queries can use an index rather than scanning whole tables. Each row shows the index name, its table, its columns, and a note on what it is for, with a checkbox showing whether it is already in place.

  • Index table with Select All / Deselect All – tick the indexes you want (Select All is the usual choice) and click the create button. The button reads Create All Indexes on a fresh site and Update Active Indexes once some exist.
  • Create / Update indexes – builds the selected indexes. Creating them can time out on very large tables; if it does, refresh the page and click again and it continues from where it left off until all are created.
  • Drop All Indexes – removes the indexes this plugin created, if you ever need to start again. (These are also dropped automatically if you deactivate the plugin.)
Note

Because deactivating drops every index, always recreate them (Select All, then Update Indexes) after any deactivate-reactivate. This is the most common reason a site “suddenly went slow again”.

See the dedicated Custom Database Indexes article for detail on what each index does and how to confirm they are being used.

Query Speed

This tab (labelled Query Speed, and Archive Pages in older versions) optimises the main query on your archive pages: the blog list, the shop page, product category and attribute pages, and any custom post type list. These are the pages where slow queries usually live. Most options here change behaviour as well as speed, so read each description before enabling it. Two are safe to turn on if you are unsure: Remove CAST on wp_postmeta and Optimise WooCommerce Group By.

  • Remove CAST on wp_postmeta – safe. Removes a redundant CAST that stops MySQL using the indexes on the meta value column. Recommended.
  • Optimise WooCommerce Group By – safe. Drops the redundant GROUP BY (and DISTINCT) that WP_Query adds, avoiding an expensive sort, or replaces GROUP BY with DISTINCT when joins are present.
  • Remove SQL_CALC_ROWS – changes behaviour. Stops WordPress counting every matching row just to show a total, which can make the query far faster, but page counts become inaccurate, so most people pair this with an infinite scroll plugin. Options let you show a fake page count or replace the last page number with a word such as “Many”.
  • Remove sort options – changes behaviour. Uses the natural database order instead of your default sort, which avoids a full sort. It is always disabled when there is an ?orderby parameter in the URL.
  • Alter main query to use EXISTS rather than LEFT JOIN – experimental, and it also removes sorting. Rewrites the main query to use WHERE EXISTS so it can avoid a GROUP BY and use the indexes properly. It never touches admin queries.
  • Remove OR check for private items on front end – changes behaviour. When logged in as admin, WP_Query also checks for private posts, which stops the index being used; removing it speeds admin browsing but hides private items on the front end.
  • Custom Query Alterations – apply any combination of the above tweaks only between two WordPress action hooks, so you can optimise a specific context (a menu, a sidebar, a content area) without affecting the whole site.

See the dedicated Advanced Archive Query Tweaks article for the safe tweaks (CAST, Group By, sort, private items), Remove SQL_CALC_FOUND_ROWS for the pagination trade-off, Rewrite LEFT JOINs to WHERE EXISTS for the experimental option, and Custom Query Alterations for the hook-scoped approach. On very large sites with heavy author, taxonomy or filtered archive traffic, see the Super Speedy Archives Add-on article, which replaces those large joins with a pre-computed lookup table.

Single Pages

This tab covers single product and single post pages. These are usually quick because they load one item, but they can be slowed by widgets that run their own SQL, in which case the Query Speed optimisations above fix them automatically. There is also one dedicated option here for WooCommerce products.

  • Remove Woo Ajax variations calculations – skips the slow code WooCommerce runs on the product detail page to count and check available variations. Useful for products with a large number of variations.

See the dedicated Speeding Up Single Product and Post Pages article for detail.

Imports

The Imports tab is aimed at making bulk imports and product updates fast, mostly by optimising work against wp_postmeta and the term counts. These options are safe to enable.

  • Defer term counting – counts the items per category once at the end, as a nightly job, instead of once per imported row. This is usually the biggest import speed boost. A Recount now button is provided.
  • Cache Post Counts – uses Scalability Pro’s own cache for post counts so they are not recalculated every time an item is created, edited or deleted during an import. Shop orders are always recounted so order admin is unaffected.
  • Optimize Action Scheduler – removes completed, failed and cancelled Action Scheduler items daily so that table does not grow out of control (WooCommerce leans on it heavily during product updates). Buttons let you empty the table and clear the logs now.
  • Optimize Product Attributes Lookup – removes a redundant column check in the WooCommerce product attributes lookup queries so they run far faster during updates.
  • Remove Image Sizes Globally – optional. Lets you choose, globally or per post type, which registered image sizes to allow when items are imported, so imports do not spend time generating sizes you never use. Nothing changes until you configure it.
  • Avoid expensive WP All Export meta_key enumeration – set to Cache or Prevent to stop WP All Export scanning wp_postmeta for every meta key when you open export settings.

See the dedicated Defer Term Counting, Cache Post Counts, Optimise Action Scheduler, Optimise Product Attributes Lookup, Remove Image Sizes Globally and Avoid Expensive WP All Export meta_key Enumeration articles for detail.

Bloat

This tab (labelled Bloat in the tab bar) collects fixes for stores with a large number of products or orders: rewriting badly performing WooCommerce and third-party queries, and caching slow shortcodes.

See the dedicated Optimise WooCommerce Updates, Cache WooCommerce Shortcodes, Ajax Attribute Editing, Switch Off Phone Home and WooCommerce Admin Query Fixes articles for detail.

WP Admin

This tab removes the table-scan-heavy widgets and dropdowns that slow down wp-admin on large stores. These caching options are safe to enable.

  • Remove Woo order summary – removes the WooCommerce dashboard order summary widget, which runs an expensive query on every dashboard load. The same figures remain available under WooCommerce > Reports.
  • Cache wp-admin WooCommerce Products Category List – caches the category dropdown on the Products admin list using Scalability Pro’s cache, which survives product edits (the default WordPress cache does not).
  • Cache user counts – caches the user counts behind the admin user filter dropdowns for 12 hours, avoiding a table scan on stores with many users.
  • Cache author counts – caches author post counts for 12 hours. This may be disabled if the underlying WordPress list table has changed and Scalability Pro needs updating.
  • Disable WooCommerce Marketplace Suggestion – stops WooCommerce running a count of recent orders on every wp-admin page just to decide whether to market its services.

See the dedicated Remove WooCommerce Dashboard Bloat, Cache Author and User Counts and WooCommerce Admin Query Fixes articles for detail.

Slow Query Log

Scalability Pro includes its own slow query log that records the URL and the stack trace behind each slow query, so you can see which page and which code caused it. This makes slow queries far more actionable than the standard MySQL slow log.

  • Enable Slow Query Log – turns the log on. For fullest coverage it can install a db.php drop-in via a symlink and add a $SPRO_GLOBALS variable to wp-config.php, so that even queries running before the init hook are profiled.
  • Slow Query Limit – the time in seconds above which a query is recorded (default 0.5).
  • Query Pattern – optionally capture every query containing a given string, with stack traces. This has a significant performance impact, so do not leave it on for long.
  • Slow Queries – the most recent logged queries, with buttons to empty the log table and export to CSV.

See the dedicated Using the Slow Query Log article for detail on reading the results.

Advanced

The Advanced tab is for moving your configuration between sites and keeping a backup. It exports the whole Scalability Pro settings block as JSON, and imports it back.

  • Export Settings – shows your current settings as JSON, with buttons to copy to clipboard or download as a file.
  • Import Settings – paste exported JSON to load it. This overwrites all your current settings, so export a backup first.

The intro screen also warns when you have too many autoloaded options in wp_options, which slows every page load. See the dedicated Managing the wp_options Table and Autoload article for how to fix that, and the Export and Import Your Settings article for moving a configuration between sites.

Recommended order

If you are working through the screen for the first time, this is the order that gives the most benefit for the least risk:

  1. Create all the indexes first. This is safe and is often enough on its own.
  2. If you are speeding up imports, enable the Imports tab options; they are all safe.
  3. Enable the safe admin caching options on the Bloat and WP Admin tabs (shortcode caches, category dropdown, user and author counts, remove the order summary, disable marketplace suggestions). Treat Switch off phone home carefully and use its allowlist.
  4. Only then work through the Query Speed options, reading each description first, because most of them change behaviour as well as speed.
  5. Turn on the Slow Query Log if you still have slow pages, so you can see exactly which queries are to blame.

If you are unsure whether an option is safe for your setup, leave it off and ask us on Discord. For the fast route, see the Quick Start Guide, and if something behaves unexpectedly after enabling an option, see the Troubleshooting article.

Further reading

Quick Start Guide
the safe-first order to work through these tabs in a few minutes
Custom Database Indexes
a deeper look at the single most important tab
Troubleshooting
what to do when an option behaves unexpectedly

Related products

Scalability ProSpeed up your whole site – big catalogues, big databases, no re-architecture

Comments

▲
▼
1/1