Performance issues with WooCommerce 10k products
Hi All,
We currently configure AC Pro on some WooCommerce sites, it runs really smooth until now: In one shop, we have approx. 10k products imported. Now, the smart filters are really slow (some minutes) to load. Default filters, also added with your plugin, load as fast as before.
The server is really fast, we tried with up to 2GB RAM allocated.
Any idea what can help?
Thanks and best Regards,
Steffen
Here’s an example query which takes really long – it’s the main query. Smart filter was “Image not set”.
LEFT JOIN as_postmeta
ON ( as_posts.ID = as_postmeta.post_id )
LEFT JOIN as_postmeta AS mt1
ON (as_posts.ID = mt1.post_id
AND mt1.meta_key = ‘_thumbnail_id’ )
WHERE 1=1
AND ( ( ( as_postmeta.meta_key = ‘_thumbnail_id’
AND as_postmeta.meta_value = ” )
OR mt1.post_id IS NULL ) )
AND as_posts.post_type = ‘product’
AND (as_posts.post_status = ‘publish’
OR as_posts.post_status = ‘acf-disabled’
OR as_posts.post_status = ‘future’
OR as_posts.post_status = ‘draft’
OR as_posts.post_status = ‘pending’
OR as_posts.post_status = ‘private’)
GROUP BY as_posts.ID
ORDER BY as_posts.post_date DESC
LIMIT 0, 20
Yeah, some filters can be slow on a large dataset and there is not much we can do since we already optimized most sorting and filtering queries as best as possible. Filtering on ‘Not set’ for a Custom Field column is slow because a left join on the post meta table is necessary. 10.000 products probably mean at least a tenfold of records for that in the post meta table. You can try to make sorting faster by disabling the feature to show empty results on our settings page. If you search for a specific image it will probably be fast, but the Not set or any query that needs to include empty (nonexistent) records as well, are heavy on performance on big datasets.
Hi Stefan,
Thanks for your quick response! Wow, I didn’t expect to hit WP / Woo limits so quickly.
As far as I know, one option would be to use elasticpress to route wp_queries to an elasticsearch server, instead of querying the WB DB.
https://github.com/10up/ElasticPress
Would your plugin still work with this setup?
Thanks and best Regards,
Steffen
Hi Steffen,
I don’t know for sure, but we try to stay as close to WordPress as possible, so if you port any WP_Query to elasticsearch, there is a good chance it will work with our plugin since most of our queries hook into the WP_Query.
Hi Stefan,
Thanks for the info!
In the meantime, we solved the performance issue, at least for now.
The reason of very slow queries was not the number of products, but the amount of product meta data. An automated product import went rogue and created approx. 120 additional post_meta fields per product. After removing these, the performance issues were gone.
So for anyone reading this: admincolumns plugin is absolutely capable of quickly sorting / filtering a WooCommerce shop with 10k products. As long as you don’t mess up your product database. :-)
Best,
Steffen
I’m experiencing the same problem.
@Steffen …how did you find and identify these extra post_meta fields per product, and remove them?
I too have just done a large product import, so… this might solve my problem, too.
Thanks,
Del.
You must be logged in to reply to this topic.