- 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
I wrote previously about the slow code that exists in WooCommerce for Onboarding tasks.
They misuse the database in 4 separate areas and I’ve already submitted a merge request to fix two of these.
Now I’ve managed to fix all of these areas inside Scalability Pro.
Table of Contents
What this new option speeds up
This will speed up 4 different calls that occur on most wp-admin pages IF your wp_cache has been wiped. Your wp_cache gets wiped whenever you edit, delete or add a product, so if you are running imports your wp_cache is getting wiped constantly.
So – the biggest impact here is going to be for users who are performing imports – it should speed up your imports significantly AND it should speed up your use of wp-admin while those imports are underway.
Why this slowness exists in WooCommerce
First – the obvious – they are counting products which causes table scans.
They attempt to mitigate this by using the wp_cache. However, when you are running imports which happens a LOT on large sites, or when you update any post or product, this cache gets wiped.
So – if you are running imports all day or editing products all day, your site (and import speed) will be significantly impacted by this badly coded onboarding code.
Slow has_products() function
The has_products() function is called twice and although it passes LIMIT 0,1 to the function, it also includes the default ORDER BY and a join to term_relationships. These things combined cause all of your products to be fetched and sorted by your default sort order prior to returning this 1 item.
I’ve implemented a function to check if the originating function was has_products() and then rewrite the SQL to perform quickly.
Slow is_new_install() function
This calls wp_count_posts() which performs a table scan and counts every product when it only needs to know that at least 1 product exists. So, I check if the origin function is is_new_install() and I rewrite this SQL to check if a single product exists which reads 1 row maximum rather than your entire database.
Slow get_homepage_template() function
This function checks if there are more than 4 products. It does so by counting the total products you have. I check for this function being the caller and then rewrite the SQL to grab the first 5 products with no order by and no group by.
Feedback Requested
This code is brand new and for the wp_count_posts function, I fake the existence of 1 item for each post_status. You’ll see something like this in your wp-admin Products list.
The wp_count_posts function actually stores the results in the wp cache storage system. If this cache does not get wiped, you will see product counts of (1) on your wp-admin products list page.
If you have a Github account, I’d appreciate it if you could upvote and/or comment on my pull request with WooCommerce so we can get this fixed at the source.
Finally, I’m looking for feedback from users of Scalability Pro who test out this feature.
- How much does it help speed up your imports?
- Does this new functionality impact any other areas negatively where the faked product count of 1 causes any issues?