MySQL Forums :: General :: Optimizing a Query on a Very Large Table


Advanced Search

Optimizing a Query on a Very Large Table
Posted by: alex reznik ()
Date: May 01, 2009 03:00PM

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

Options: ReplyQuote


Subject Written By Posted
Optimizing a Query on a Very Large Table alex reznik 05/01/2009 03:00PM
Re: Optimizing a Query on a Very Large Table Peter Brawley 05/02/2009 09:50AM
Re: Optimizing a Query on a Very Large Table Rick James 05/02/2009 01:12PM
Re: Optimizing a Query on a Very Large Table Peter Brawley 05/02/2009 04:01PM


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.