script causing cpu max limit - get_audiences_list()

[capnhairdo]

The query in question works fine on a small dataset, but in the case of large amounts of order and/or customer data the query is not adequately optimized. So, to keep it will require fine-tuning it... It's really not high on my list of priorities of things to "fix".

I'm a little bit horrified that ZenCart is including SQL queries that are expected to fail when used under real world conditions and need to be tweaked by end-users to work correctly.

We have a 1.38a installation with about 16K customers and 50K customers. (Not especially high numbers for an ecommerce site.) As of today, our entire site was locking up and timing out, and I spent half the day tracking it down to this SQL query. According to the logs, this query was taking of 2,000 seconds to run. That's a HALF HOUR.

gilby's suggestion to replace the date_purchased field with customers_id field is a good one. Seems to work just as fast on orders_id, probably because both those fields are defined as non-NULL.

DrByte's suggestion to add an index on customers_id helped even more (though not necessary if you use orders_id instead of customers_id). Using an indexed column got the query running in .02 seconds. Quite a difference from 2000 seconds.

Hope this fix did become a priority and was implemented in 1.3.9.

[gilby]

It was :)

And.... You also should upgrade as well

You will find this and many other "fixes" are included as well to improve performance.

[DivaVocals]

Hope this fix did become a priority and was implemented in 1.3.9.

Not to speak for the good doctor, but I do believe you misunderstood what he meant when he stated "It's really not high on my list of priorities of things to "fix" "..

It's likely he meant that it was not a priority to fix thin in v1.3.8a since v1.3.9 was the thrust of the Zen developers focus and the issue would be addressed in THAT release... DrByte can of course confirm my interpretation.. this is how I took his words..

Given that DrBtye also stated that this was addressed in Zen Cart 1.3.9:

Or just add a distinct index on orders.customers_id to improve performance considerably. (v1.3.9 does this during the upgrade):

and as gilby posted it WAS addressed in v1.3.9, it seems that the thing to do to correct your situation is to upgrade your 1.3.8a shop to the latest version of Zen Cart (v1.3.9h as of this post) or apply the changes posted (as it appears you have done)