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

[Bill Kendrick]

Ok, so I've got a few queries our website does that
are used to determine which products are 'top sellers'
in the past 90 days' worth of sales.

Right now, this involves joining the product table
with the orders table, by way of an items-in-the-order table.

In other words, to figure out what sold the best in the
last 90 days, it's something like:

  SELECT * FROM products
  JOIN orderitem ON products.id = orderitem.productid
  JOIN orders ON orders.id = orderitem.orderid
  WHERE orders.timestamp >= { 90 days ago }

As time goes on, our records or orders, order items, and
actual products all keep growing.  That means this task
gets more and more intensive.


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,
to simplify the "top sellers" queries.  They'd hit
a single stats table (which could, on a daily basis,
be cleared of any >90-days-ago entries), rather than
having to JOIN two large tables that go back to day 1.


My current problem -- no doubt due to lack of brain power
at the end of the day -- is how to take the CURRENT data
from the orders and orderitem tables so that I can do an
initial population of a new stats table.

Our "views" stats table, for example, is something
along the lines of:

  productid, date, views

where those first two columns act as a key.  (Date is at the
'day' level of granularity.)


And we count a view like so:

  INSERT INTO views (productid, date, views) VALUES (###, NOW(), 1)
  ON DUPLICATE KEY UPDATE views=views+1

In other words, the first time a product is viewed on any given day,
we insert a new row, otherwise we update its existing row.


Anyone feel like flexing their MySQL muscles and provide some
ideas on how to populate a similar table (tracking sales) based
on the orders/orderitem table combo explained above?

Thanks.  Sorry for asking you to do my (home)work for me.


-- 
-bill!
Sent from my computer