Calculating average (AVG) and grouping by week on large data set takes too long

Question

I'm getting average prices by week on 7 million rows, it's taking around 30 seconds to get the job done.

This is the simple query:

SELECT AVG(price) as price, yearWEEK(FROM_UNIXTIME(timelog)) as week from pricehistory where timelog > $range and product_id = $id GROUP BY week

The only week that actually gets data changed and is worth averaging every time is always the last one, so this calculation for the whole period is a waste of resources. I just wanted to know if mysql has a tool to help out on this.

Answer 1

Have you tried indexing the fields?

I am no DDL expert in MySQL, but in this case I'd say that timelog should have a clustered index, then non-clustered indexes should be declared for product_id. Also it would be a good idea to add a new field to the table, for storing the "week" value and index it as well. It would take slightly more space, but that way you would avoid making the same calculations each time.

Answer 2

I would suggest creating a new composite BTREE index on (product_id, timelog) and change the order of conditions in WHERE clause:

SELECT
    AVG(price) as price,
    yearWEEK(FROM_UNIXTIME(timelog)) as week
from pricehistory
where product_id = $id AND timelog > $range 
GROUP BY week

If you already have a BTREE index on (product_id) only, just extend it to (product_id, timelog)