Statistics grinds site to a halt

[keat]

I'm back on lockdown, thanks for taking the extensive time.

We learned something at least.

[bsandall]

6 hours ago, Noodleman said:

The queries probably shouldn't run on page load in this case, but perhaps on demand via a "refresh stats" button on the statistics page. This can then run the queries, gather the results from each and store the results in a database statistics table. the reports can then pull the derived values from the table rather than calculate it every time.  This isn't too much of an issue as it's only going to impact stores with a large data set.

This was my option #1 if fixing the indexes didn't resolve it:

On 5/17/2017 at 8:31 AM, bsandall said:

1. Create a summary table (and regularly update it) containing a running tally of quantities sold so that the database doesn't have to actually SUM potentially tens of thousands of rows. This wouldn't be a bad idea for core CubeCart to implement.

It can easily be kept up-to-date after the initial calculation whenever an order is created, modified, or deleted, perhaps with a total recalculation on demand feature.

[Noodleman]

11 hours ago, bsandall said:

This was my option #1 if fixing the indexes didn't resolve it:

It can easily be kept up-to-date after the initial calculation whenever an order is created, modified, or deleted, perhaps with a total recalculation on demand feature.

That sounds more appropriate. I think it's an excellent idea.