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!