SQL queries taking more than 1 min

Hi and thank you for your report

This is strange, indeed!

Do you think you could share some stats on wishlists tables?
How many wishlists and items do you currently have on your store?

Query that retrieves wishlists’ items, joins on posts table, so the number of posts on your shop could also affect the loading time

Thanks for your reply.

You can download both tables here: https://bit.ly/2QJKaed
If I’m getting this right, we have 145K products that are added to a wishlist and 14K wishlists. Of those products, I think many might have already been sold and are out of stock (and won’t be stocked again).

We sell secondhand products, so every product is unique. Every once in a while we remove some of the products from Woocommerce (so that our store does not collapse). We have right now over 60K products.

As I can see, whenever someone has more than 50 products on their wishlist, the query takes almost one minute to load!

Is there a way to fix this?

Thanks!

To give you more context, it just happened again.
This is someone who has 165 products on their wishlist (from what I can see on NewRelic).

View post on imgur.com

The weird thing is that the parts that are highlighted in yellow are not recorded on NewRelic, they are something internal and that is what causes the huge slow down.
(ref: https://imgur.com/a/90gsAmm)

It appears as if there’s some kind of infinite loop or memory leak, because the response time is almost infinite and CPU usage is over 500%.

Please, your help is much appreacited, turns out that our best customers are the ones that are experiencing this issues right now (because they have the biggest wishlists)

Thanks,

Santiago

EDIT: Not sure if this might have something to do, but 65K / 145K rows that are on wp_yith_wcwl correspond to products that have been deleted from Woocoommerce, so they take it nowhere.
Is that case considered in your plugin? I think it should be reasonable to remove all entries on the wishlists from products that are removed from Woo.

• This reply was modified 5 years ago by svaldesm.
• This reply was modified 5 years ago by svaldesm.

UPDATE:

I modified the database to remove any ID of products that were removed from the store.
There are now 81K rows on the wp_yith_wcwl table (I made sure to alter the autoincrement and also to make IDs consecutive)

I managed to speak with one of the customers who had a big wishlist.
It does not work for her at all. Even after removing this products (that might have been the ones causing the issue) when she tries to enter it takes years to load, and trying to remove one product freezes the browser completely.

From my end I’m not sure there’s something else to be done.
We have 50K products and ~15K customers, I’m not sure if this is a huge volume for you guys, but so far with other plugins it is very manageable.

If you need access to my staging site, I’ll gladly send it to you so you can check by yourself.

Thanks!

• This reply was modified 5 years ago by svaldesm.

Hi again

I have some hypothesis on what could cause the issue; let me share them with you:

1. The huge number of products + items could definitely be the source of the problem
Since read_items() performs a join between posts and yith_wcwl, the query could take a while, since it needs to process a lot of data from the two tables in order to create result set
2. From my understanding, you have a lot products that cannot be purchased on yuor store
I suppose, but I’m guessing here, that you hide those products from your catalog

Wishlist is designed to retrieve hidden products, and exclude them from the list, at the moment of printing it
If you have a huge number of hidden products on the store, the query that retrieves items could be heavely affected

Now, let’s see how could we proceed to takle these two scenarios

1. Unfortunately I have no easy fix for this case
What I’m planning to do, in next days, is to deploy a Unit Test, that will allow me to generate an arbitrary number of products, wishlists and items, in order to double check how well each query will hold up with increasing data set
Hopefully this analysis will bring some optimization to the code, but it may take some while

2. In this case I think that it could be useful to move the check for hidden products from the query to the PHP execution

You could do this adding the following snippet of code at the end of functions.php file of your theme or child (make sure to use wishlist 3.0.7 or greater)

add_filter( 'yith_wcwl_remove_hidden_products_via_query', '__return_false' );

I’m open to any suggestion, and of course I’ll let you know as soon as I have news
In the meantime, just in case I need to quickly import the tables you shared with me, do you mind expoting them in CSV format?

Thanks for your answer.

We are a store that sell secondhand products, so any product that is sold won’t be available a second time. What we do from time to time is to remove those products so that only in-stock products are there. We don’t have hidden products.

According to your description, I think it’s very slow because of the amount of products. What I don’t quite understand is why other plugins work fine with that level of products but this has troubles. Others that work fine are FacetWP, Scalability Pro, SUMO Rewards and Points, CodeCanyon Dynamic Pricing and Discounts, and others more. All of them have different purposes, but my point is that the amount of products we have shouldn’t have THAT level of performance impact because those plugins are able to cope with it fine.

To be honest, I’m not a developer myself, I do a few tricks but far from an expert.
I’m not sure how to proceed with the performance on impact, but maybe you can check plugins that have similar scalability problems and are greatly built, as FacetWP for filters (works amazing!).

Maybe a few tricks:
– using indexes
– using pagination (depending on the situation it may help)
– avoiding running querys that perform full scans

I’ll be happy to test any new version and you can feel free to use my staging site to test if there’s indeed an improvement on performance.

You can download the data here: https://bit.ly/3dudZcj
The customer I spoke to (ID 3303) has 1900 products on her wishlist, which is far from normal, but still.

Also, I’m not sure why there are lots of products assigned to customer ID = 0 (does not exists)

Thanks for your help!

– Santiago

Hi there,

This is kind of urgent. Customers with huge wishlists are dropping my site at times.
They wait for the list to load over 200sec and it uses all CPU and RAM available.
Is it possible to limit that somehow, or use a pagination system?
It really is a big deal for us.

Maybe a few things that might help:
– limiting the amount of items (as Aliexpress does) per wishlist
– have an option to remove all out of stock items from wishlists in a quick way (we do it right now via database)
– limit the time a query can run (or have an option for that)

Thanks!

– Santiago

• This reply was modified 4 years, 12 months ago by svaldesm.

Hi Santiago

I understand that this is an urgent topic, but unfortunately testing and optmizing plugin to handle huge number of products and items in list may require a while

Anyway, since you’re metioning it, plugin already uses pagination to avoid loading all items in the wishlist at once
This is an optional feature that can be enabled by editing wishlist page, and replacing default [yith_wcwl_wishlist] shortcode with something like this

[yith_wcwl_wishlist pagination="yes" per_page="3"]

Obviously, you can replace 3 with any number of items you’d like to show per page

Hi, I appreciate your answer. I’ll try that.

For now, we reduced the amount of products on the biggest lists.
I was going to suggest some things that could be interesting for the plugin:

– Be able to remove all out of stock products at once from the lists
– Change the default order that is used for showing the products. Right now it’s ASC from date of publication, but that is counter intuitive because you will always see the oldest products from your list. I think that could be something you can choose on admin
– Limit the amount of products per list. If your list is full, I’ll start replacing old products with new ones or just block the add process.

Some of those measures could help others as well.

Thanks for your help and please keep me posted if there’s any advance in performance for bigger lists.

Hello,
Just came here to report the same problem,
our wp_yith_wcwl table has 1 million row right now, we have 10k products, and our cpu usage was reaching 300%, after weeks of debugging (we had to double our database server resources while debugging since the server was crashing) we noticed this issue, after disabling the plugin, our cpu usage went back to 11%.

@svaldesm if you can please share me any queries you used to clean the table, the wishlist feature is off right now, but would like to enable it back till @yithemes finds a solution.

@404eng Unless you expect this kind of traffic on your wishlist, as svaldesm did, your problem is more related to this topic

As you can read there, wishlist 3.0.10 will contain a fix for this kind of spam activity, and will prevent such problems
We’re doing some tests, and hope to release this feature very soon

Regarding unecessary items in your db, a good way to delete them would be to delete all session wishlist (wishlist for guest users)
A way to do this is to perform the following queries on db

DELETE FROM wp_yith_wcwl WHERE wishlist_id IN ( SELECT ID FROM wp_yith_wcwl_lists WHERE user_id IS NULL ); 
DELETE FROM wp_yith_wcwl_lists WHERE user_id IS NULL;

Please, note that only an expert user should apply direct changes to db; before proceesing make sure that you fully understand what the previous queries do, and to backup your dataabase
Use this queries at your own risk

Hi @404eng ,

Our business is a bit different, that’s why the solution is a bit tricky.
We sell secondhand products, so we get lots of products out of stock quickly (because there’s 1 unit per product). That makes having out of stock products in the wishlist useless for us (that product won’t return).

What I did was to download the full table and edit it on Excel, removing all products that were out of stock or removed on the site.

The second thing we did was to realize that customers were having, despite removing out of stock products, huge lists. We then reduced those lists manually (it was a pain) to 50 products max. Not elegant but it worked and we send them the remaining products via email.

That’s why I suggets to @yithemes to:
– increase performance (I know it takes time)
– have the ability to remove all ouf of stock products from backend
– limit the number of items on a wishlist, and after that, start replacing elements

Another thing I would like to propose @yithemes is to be able to configure the order of the products being shown. I think it’s more useful to have newer products on the wishlist being shown first rather than old ones. But that may be something you could configure.

Cheers to both

Thanks a lot for your reply @svaldesm, I did the same as you did eventually.
besides limiting the items count per user, another thing that can help is having an option to disable adding to wishlist for guests (non logged in users).
as after I tried the queries provided by @yithemes I noticed that most of my rows belonged to ‘null’ users (mostly bots I think as @yithemes posted a link to the other issue).
Cheers and thanks for your support, both of you.