WP-e-Commerce
WP-e-Commerce copied to clipboard
wp-e-commerce
Every product being queried by WP_Term_Query->get_terms() on every page load.
I noticed a site really getting slowed with slow queries. After investigating, I noticed that on every page, post, product, a query of...
SELECT t., tt., tr.object_id FROM agsterms AS t INNER JOIN agsterm_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN agsterm_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('product_tag', 'wpsc_product_category', 'wpsc-variation') AND tr.object_id IN (85830, ..., ..., ...) ORDER BY t.name ASC
...where the ids in tr.object_id IN (85830, ..., ..., ...) were all of the ids of every published wpsc_product.
So, essentially, for every single front end page load, every single product along with tax information is being queried and loaded for some reason, even if the page isn't displaying any products. (I though it was just this site, but it's happening on another site I have access to as well, but isn't causing any noticeable slow down, as there's only 40 products or so, with basically no variations.)
I can confirm I am getting something similar too - checked using the Query Monitor plugin.
I'm also getting the following post meta query which seems to be generated by WPEC on every page querying every product ID:
SELECT post_id, meta_key, meta_value
FROM wp_postmeta
WHERE post_id IN ( 61953, 30210 ,58626, ...
I have noticed that, if you enable pagination in the store settings, it will limit the query to whatever number you ask it to show per page. It helps if you have a lot of products, but may not be what you really want, and it's still a query that doesn't appear to be necessary, even on pages displaying products, as a separate query takes care of displaying them.
I have some changes in the fork that I run that eliminates all variation related queries if a store does not use variations. I recall the the benchmarking I did more than a year ago showed a reduced database load of about 35%.
If this is something we would want in core I could create PR for it.
Let me know.
@JeffPyeBrook I feel like I reviewed that branch at some point...and I thought I recalled some issues with the implementation...but conceptually, I think it could work well.
@JeffPyeBrook Ahh, I found it (i think) - https://github.com/JeffPyeBrook/WP-e-Commerce/commits/dont-make-variations-calls-when-no-variations
So this is definitely a good idea, partially. It solves a specific use case (sites with no variations) that is worth addressing. The idea of having an autoloaded option to determine variaimtion use is great! But running the check on the shutdown hook when is_admin() is true (which would include all AJAX requests) is not awesome. I would see a better place for that as the save_post hook (or save_wpsc-product) - and possibly something like the initial activation hook as well.
But it doesn't improve performance for sites that do use variations, which I think would be a worthwhile endeavor as well.
What I think makes sense to hook the taxonomy update API on the admin side. When taxonomy terms are saved, we should check to see if there are any terms in the variation taxonomy with product counts. If there aren't any, add an autoload option that means variations are disabled, if there are delete the autoload option.
Front end could check the autoload option's value before making a taxonomy query.
I also would add a config file option that forces that option value to disable variations..
There other things that happen every page need to be addressed. The highest priority should be re-calculating shipping quotes. This can hit databases, make network REST calls and lots of other stuff. I have a bunch of hacks to work around the specific impacts, but nothing PR worthy.
Product queries on my sites are voluminous but have very low impact. The WordPress cache seems to do a really good job at keeping the correct results handy.
