More speed boosts for wp-admin and imports with Scalability Pro upgrade

Dave Hilditch
Talk to me

The WordPress dashboard can get quite slow as your website grows which is why we’re always on top of it and adding more code to Scalability Pro to keep your dashboard running quickly. This article describes some upgrades for import and wp-admin speed released in Scalability Pro 4.94

Remove SQL_CALC_ROWS from edit.php option

If you want a fast dashboard, in this case on the edit.php pages where you list orders, products, posts, pages, you can use the new option we have created to fake pagination on edit.php.

SQL_CALC_ROWS on the edit.php page performs a table scan against your database, demonstrating WP-admin speed boosts with Scalability Pro.
SQL_CALC_ROWS on the edit.php page performs a table scan against your database
Counts are still correct with SQL_CALC_ROWS removed, showing faster edit.php queries in WordPress.
Counts are still correct with SQL_CALC_ROWS removed since edit.php uses a separate cached query to fetch counts per post_status, and the query becomes basically instant

Enable the option by visiting Settings > Scalability Pro > Archive Options and enable ‘Fake pagination count on edit.php’

Remove SQL_CALC_ROWS to speed up all edit.php pages for posts, products, orders, and CPTs in WordPress.
Removing SQL_CALC_ROWS on edit.php makes these pages scale regardless of how many posts, products or orders you have

SELECT DISTINCT fix when not needed

On a recent performance analysis job we found some queries perform a SELECT DISTINCT in conjunction with LIMIT 0,1. In this case, the SELECT DISTINCT is redundant since we’re only returning one row anyway. This code has been added to Scalability Pro:

// if LIMIT 1 or LIMIT 0, 1, we can safely remove DISTINCT from the query which would otherwise cause a tablescan
add_filter('posts_clauses', 'spro_remove_distinct_limit_1', PHP_INT_MAX, 2);
function spro_remove_distinct_limit_1($clauses, $wp_query) {
    if (trim($clauses['limits']) == 'LIMIT 0, 1' || trim($clauses['limits']) == 'LIMIT 1') {
        $clauses['distinct'] = str_ireplace('DISTINCT', '', $clauses['distinct']);
    }
    return $clauses;
}

You do not need to do anything to enable this new SELECT DISTINCT fix – since it benefits across the board with no danger, we’ve enabled it by default if you have Scalability Pro activated.

Remove post_count per status on edit.php

Do you ever use the little status counts at the top of the edit.php page?

WordPress products list showing counts, with option to remove for performance improvements.
If you don’t use these counts, you can remove them and we’ll add a link so you can temporarily re-activate them

They can be useful, but in most cases they are not used. Maybe if you need to visit the Trash to empty your trash bin. Probably in the orders page they are more useful so you can filter to pending orders.

Query monitor showing expensive query for product/order/post totals in WordPress admin.
Every time you visit edit.php, if any type of post has been added since the last time wp_count_posts was called then this query will run again

These counts per post_status are clearly useful, especially when it comes to orders.

Orders post_status counts on edit.php page in WordPress admin.
Orders post_status counts on edit.php

But perhaps you do not need them to be bang up to date accurate? Perhaps you just need those links to be clickable? If you have a high volume store, say you are importing products or say you have many orders arriving, this expensive wp_count_posts code will run time after time after time thrashing your database with expensive table scans.

In my experiments on client sites, I regularly see MULTIPLE simultaneous calls to wp_count_posts when 5 or 6 pages are opened at the same time with this count taking 30 seconds or more, depending on how many items you have in wp_posts.

In WordPress 6.1 they are going to add DB caching of repeated queries, but for sure they will have some cache invalidation built in there which would flush this cache. Bear in mind that wp_count_posts is already cached:

Having one slow page load every hour is better than having slow page loads on every page your admins use. It drives admins crazy when they are trying to administer products or orders and the page to simply list the items takes forever to load.

In WordPress 6.1 they are going to add DB caching of repeated queries, but for sure they will have some cache invalidation built in there which would flush this cache. Bear in mind that wp_count_posts is already cached:

WordPress function wp_count_posts() trying to fetch counts from cache if not flushed.
WordPress will try to fetch counts from the cache if it has not been flushed

However, a lot of occasions exist where the cache will be flushed.

WordPress function wp_count_posts() trying to fetch counts from cache if not flushed.
wp_cache_flush is called from MANY locations

WordPress 6.1 promises many performance boosts however, one of them being cached DB queries. So, it’s possible that this issue will be resolved within 3 weeks.

Because of that, I’m going to defer adding the cache of wp_count_posts. Instead, for now, I have a pull request submitted to WooCommerce to fix their similar and related abuse of wp_count_posts and if WordPress 6.1 and their DB caching doesn’t fix this then I will return to it.

You can see my WooCommerce pull request here for a couple of other instances of wp_count_posts abuse here: https://www.superspeedyplugins.com/2022/10/12/tracking-down-the-origins-of-slow-sql-code-in-wordpress/

The fixes mentioned above are all included in Scalability Pro 4.94 which should be released by the time you read this article and if WordPress 6.1 doesn’t fix this mis-use of wp_count_posts then I will return to it and figure out how to cache it properly or bypass it completely so we have the links without the counts.

Be the first to comment and we'll reply right away.

Leave a reply

Super Speedy Plugins
Logo