WooCommerce SQL Optimization

Author
Recent Posts

Talk to me

Author of the WPI and Super Speedy Plugins performance guides and creator and coder of the plugins sold by Super Speedy Plugins.

Talk to me

Investigating Slow Queries

The slow query seems to be originating from something block related, but the underlying SQL has a redundant GROUP BY command.

SQL query displaying original long-running WooCommerce query before optimization

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.

SQL query showing optimized query execution time reduced from 10.50 seconds to 0.02 seconds for WooCommerce SQL Optimization using Scalability Pro

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.

SQL query results showing different IDs returned due to identical post_date values in WooCommerce

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.

Call stack in query monitor indicating slow code related to WooCommerce blocks

SQL query displaying taxonomy and term details for product types in WooCommerce.

I then connected VS Code to my remote server and used xdebug to add a timer and a conditional breakpoint to the code.

Code snippet from product-hero.php block indicating slow query source in WooCommerce.

So, this slow code is coming from the product-hero.php block/pattern.

WooCommerce block template showing use of product_id in product-hero block.

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.

PHP function code optimizing WooCommerce queries by removing DISTINCT and GROUP BY clauses.

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:

Dashboard view showing improved query performance in WooCommerce after removing GROUP BY clause.

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.