MySQL Forums
Forum List  »  General

Re: Optimizing a Query on a Very Large Table
Posted by: Rick James
Date: May 02, 2009 01:12PM

Do it in two steps:

The first step is to get counts for each day:
CREATE TEMPORARY TABLE ByDate
SELECT  LEFT(file_last_access, 10) AS Age,
        COUNT(*) AS Ct
    FROM  `HugeFileTable`
    GROUP BY  LEFT(file_last_access, 10)

Better yet -- if old data is not changing, you should really make that a permanent table, and add new data each night. This way, you don't have scan the Huge table each time.

(Leaving step 2 as an exercise -- use ByDate and DateRanges. ByDate will be so much shorter that any inefficiencies in the JOIN will probably be tolerable.)

Comma-join and JOIN are synonyms; what was your point, Peter?

Options: ReplyQuote


Subject
Written By
Posted
Re: Optimizing a Query on a Very Large Table
May 02, 2009 01:12PM


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.