- ChatGPT really sucks at SQL - August 9, 2024
- We’ve launched our newsletter again - July 24, 2024
- Full Hello Elementor and Elementor Pro FSE compatibility added for Super Speedy Filters - May 30, 2024
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
Table of Contents
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.
Enable the option by visiting Settings > Scalability Pro > Archive Options and enable ‘Fake pagination count on edit.php’
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?
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.
These counts per post_status are clearly useful, especially when it comes to orders.
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:
However, a lot of occasions exist where the cache will be flushed.
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.
