Optimizing a Query on a Very Large Table

[alex reznik]

Hi All,

I've got a very large table (~100Million Records) in MySQL (v5.0.45, all tables are MyISAM) that contains information about files. One of the pieces of information is the accessed date of each file.

I need to write a query that will count the number of files that fit into specified date ranges. To do that I made a small table that specifies these ranges (all in days) and looks like this:

    DateRanges
    range_id   range_name   range_start   range_end
    1          0-90         0             90
    2          91-180       91            180
    3          181-365      181           365
    4          366-1095     366           1095
    5          1096+        1096          999999999

And wrote a query that looks like this:

    SELECT r.range_name, sum(IF((DATEDIFF(CURDATE(),t.file_last_access) > r.range_start and DATEDIFF(CURDATE(),t.file_last_access) < r.range_end),1,0)) as FileCount
    FROM `DateRanges` r, `HugeFileTable` t
    GROUP BY r.range_name

However, quite predictably, this query takes forever to run. I think that is because I am asking MySQL to go through the HugeFileTable 5 times, each time performing the DATEDIFF() calculation on each file.

What I want to do instead is to go through the HugeFileTable record by record only once, and for each file increment the count in the appropriate range_name running total. I can't figure out how to do that....

Can anyone help out with this?

Thanks.

(Sorry if this is the wrong place for this type of question.)