MySQL Forums
Forum List  »  Performance

How to somehow use index for group by expression
Posted by: Tim Stowell
Date: September 07, 2012 10:24PM

Hello,

I know Mysql won't allow expression indexes, but I have a situation where I can't seem to find a way around it. Without using an index my performance isn't scaling well (explain shows a temporary table and filesort). Basically I have a table where each row contains a certain product, the date and timestamp of when it was purchased, and the count purchased.

I'm trying to find how much of a given product was sold during a repeating X days period. I get the unix timestamp of the date, subtract the timestamp of the earliest date in the query range, then divide by (X * 86400) where 86400 seconds = 24 hours. This gives my "grouper" numbers that I can group by later on.

The query is below:

select
description as name,
sum(`count`),
max(date(transdate)),
ceil((UNIX_TIMESTAMP(`timestamp`) - 1306382221) / (86400 * 100)) as grouper
from
reporting
where
UNIX_TIMESTAMP(`timestamp`) between 1323595130 and 9999999999
and transtype in (0 , 1, 2, 10, 11, 12, 13)
group by name , grouper
order by name , `timestamp`

Unfortunately I don't think I can pre-calculate these grouper values in another table for indexing. Thanks for any help!

Options: ReplyQuote


Subject
Views
Written By
Posted
How to somehow use index for group by expression
2072
September 07, 2012 10:24PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.