- Customer with 50,000 products on shared hosting transforms the speed of their site from 4s to 150ms - March 27, 2024
- Lightning Fast Search for Gutenberg Link Builder & wp-admin Post and Page Search - March 22, 2024
- Making the WPNotif plugin faster - March 22, 2024
We’ve been busy setting up our Speed Test environment so we can exhaustively measure all the themes and plugins out there and discover more performance enhancements.
While running our tests, we had the Kadence theme installed and discovered a 10s query running every time on the wp-admin pages.
Table of Contents
Investigating Slow Queries
The slow query seems to be originating from something block related, but the underlying SQL has a redundant GROUP BY command.
Typically, a GROUP BY statement is used to grab counts of things per segment, but in this case the WordPress query builder (WP_Query) is using it to guarantee uniqueness.
However, notice the LIMIT 0, 1 at the end. If you have LIMIT 0,1 you are grabbing only 1 row, therefore you definitely do not need to force uniqueness.
You can see the query speed has improved from 10.50 seconds to 0.02 seconds for this million product test store.
However, you can also see that the ID returned is different. I needed to investigate a little further to ensure this wouldn’t cause any damage, and to find out why the ID is different.
Now you can see that these 2 products have an identical post_date for when they were created. Because we are ordering by post_date, the database can actually return these rows with identical post_dates in any order it likes, typically driven by the query plan the query optimizer chooses to use.
Investigating the source of this slow query
Diving a little further into the code, we find out that it’s grabbing the latest product id from simple, grouped, variable or external types (those are the only ones registered on our test site) and then doing something with that to register a block pattern.
I then connected VS Code to my remote server and used xdebug to add a timer and a conditional breakpoint to the code.
So, this slow code is coming from the product-hero.php block/pattern.
Fixing the slow query using Scalability Pro
The WC_Product_Query uses WP_Query under the hood, so that’s something that Scalability Pro can optimize.
Now, if you have Scalability Pro active, whenever LIMIT 1 or LIMIT 0, 1 is present in the query we remove both the DISTINCT and GROUP BY clauses since they are redundant and cause table scans.
Analysing the performance improvement
Here you can see the results:
Manually fixing this yourself
If you don’t have Scalability Pro, you can see a screenshot of the ‘posts_clauses’ filter above. The top part where I check the ‘limits’ clause and clear the ‘distinct’ and ‘groupby’ clauses are the parts you need to fix this. Add this filter to your functions.php.
Scalability Pro 5.24
WooCommerce keep adding queries like this which cause table scans which on small sites are no issue, but on larger sites become a real pain for admins.
Install Scalability Pro 5.24 or later or manually alter your functions.php to restore wp-admin performance to circa 1s on your wp-admin pages.
== Changelog == = 5.24 (22nd August 2023) = * Added new detection of unnecessary GROUP BY when LIMIT 0,1 is being used - this seems to happen in imports and causes a table scan of wp_posts, so this should result in a good boost for imports on large sites * Added further improvement for 'defer term counting' - when a new item is published, that post types cache is wiped - this leads to slower imports since an entire recount can happen after every import - The change has modified this query to fetch data from our own cache table which stores the counts for 24 hours if you enable the defer term counting option * Added Optimize Product Attributes Lookup functionality to the Imports tab - WooCommerce maintains the product attributes table EVEN IF you deselect to use it - One outstanding issue here on larger sites is the SQL where they check WHERE product_id = 984353 OR product_or_parent_id = 984353; - only the product_or_parent_id needs to be checked in this case and then an index gets used - This functionality alters this SQL query if you enable the option on the Imports tab
If you’d like to talk to us about this, come join our Discord server. You should also check out our Scalability Pro reviews.
