l i n u x - u s e r s - g r o u p - o f - d a v i s
Next Meeting:
July 7: Social gathering
Next Installfest:
Latest News:
Jun. 14: June LUGOD meeting cancelled
Page last updated:
2009 Dec 01 22:18

The following is an archive of a post made to our 'vox-tech mailing list' by one of its subscribers.

Report this post as spam:

(Enter your email address)
Re: [vox-tech] MySQL question: making stats out of orders
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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

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,

> 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.


vox-tech mailing list

LUGOD Group on LinkedIn
Sign up for LUGOD event announcements
Your email address:
LUGOD Group on Facebook
'Like' LUGOD on Facebook:

Hosting provided by:
Sunset Systems
Sunset Systems offers preconfigured Linux systems, remote system administration and custom software development.

LUGOD: Linux Users' Group of Davis
PO Box 2082, Davis, CA 95617
Contact Us

LUGOD is a 501(c)7 non-profit organization
based in Davis, California
and serving the Sacramento area.
"Linux" is a trademark of Linus Torvalds.

Sponsored in part by:
Appahost Applications
For a significant contribution towards our projector, and a generous donation to allow us to continue meeting at the Davis Library.