Support

Search results for ""

Sorry, no results found. Perhaps you would like to search the documentation?
All Topics
Steffen Dressler

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

3 months ago
Steffen Dressler

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

3 months ago
Stefan
Developer

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.

3 months ago
Steffen Dressler

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

3 months ago
Stefan
Developer

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.

3 months ago
Steffen Dressler

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

2 months, 3 weeks ago

You must be logged in to reply to this topic.