[vox-tech] MySQL question: making stats out of orders

[Bill Kendrick]

On Tue, Dec 01, 2009 at 08:04:31PM -0600, Chanoch (Ken) Bloom wrote:
> Is this task getting more intensive?

I think so, but it's hard to tell, with other things going on
at the same time.


> If is (and the number of
> transactions per day hasn't grown significantly),

Site traffic has been increasing steadily.


> then you probably don't have indices set up correctly.

Highly likely.  Most of these tables were set up prior to my
taking over maintenance of all this, and any similar ones were
no doubt based on the ones the previous developer designed.


> I think that having an an index on orders.timestamp,
> and an index on orderitem.orderid (in addition to the primary keys
> products.id and orders.id) should optimize this query,

Ok, I'll look into that.  Thanks!


> but it always
> pays to determine what's going on with EXPLAIN SELECT.

Good idea.


> If that still doesn't speed things up enough, maybe consider using a
> table with the MERGE storage engine to move old entries from the
> orders table and the orderitem table into archives. Then only query
> the table full of recent order data for queries, and only query the
> other tables in the merge when you need to.

Products are virtual, so a buyer's access to them are determined
by the orders, so I'm not going to change how all that logic works. :)


> > Now, we've got some statistics tables set up for other
> > purposes -- how many times a product was viewed,
> > as well as when any particular logged-in-users last
> > visited particular items, which allows us to do the
> > "people who recently looked at X also recently looked at Y".
> > 
> > I'm thinking we could do something similar for sales,
<snip> 

> I'm not really clear on what you're looking for here. We need a better
> idea of what stats you're running to know how to structure the table.
> 
> It would seem pretty simple to create the analagous table for orders:
> 
>     orderstats consisting of productid, date, orders
> 
> and every time you complete an order, you run the query:
> 
>     insert into orderstats(productid, date, orders) VALUES (###, NOW(),
>     QTY) on duplicate key update orders=orders+QTY

Yes, that's great for today-on, but the moment I switch to using this
new way of recording purchases, there won't be any data.

That is, unless I start collecting data for the next 90 days BEFORE
changing how the "top sellers" are determined.

What I'm looking for is a way to populate a new 'order stats' table
based on the [last 90 days', or whatever, of] existing orders.


Thanks!

-bill!