Should work fine. Quick test from a large sales test table (1.9 million rows): mysql> select count(*) from sali; +----------+ | count(*) | +----------+ | 1983026 | +----------+ 1 row in set (0.00 sec)
mysql> select salidate,sum(saliQtySold) from sali where salidate > DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY) group by to_days(salidate); +---------------------+------------------+ | salidate | sum(saliQtySold) | +---------------------+------------------+ | 2004-03-17 13:44:37 | 2 | | 2004-03-24 14:40:33 | 2 | +---------------------+------------------+ 2 rows in set (0.00 sec) mysql> explain select salidate,sum(saliQtySold) from sali where salidate > DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY) group by to_days(salidate); +-------+-------+---------------+----------+---------+------+------+-- --------------------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+-------+---------------+----------+---------+------+------+-- --------------------------------------------+ | sali | range | saliDate | saliDate | 8 | NULL | 70 | Using where; Using temporary; Using filesort | +-------+-------+---------------+----------+---------+------+------+-- --------------------------------------------+ 1 row in set (0.00 sec) mysql> ----- Original Message ----- From: "Jeremy Zawodny" <[EMAIL PROTECTED]> To: "Pete McNeil" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, May 05, 2004 3:00 PM Subject: Re: Range query on datetime with index - any optimization? > On Wed, May 05, 2004 at 10:53:13AM -0400, Pete McNeil wrote: > > Hello folks, > > > > I'm usinng MySQL 4.0.17. > > > > I have a table something like: > > > > RuleID int, > > GMTBase datetime, > > Credited bigint, > > ... > > > > I have an index built on GMTBase. > > I have rougly 8 million rows. > > > > GMTBase stores a datetime for the top of the hour on a given date. > > > > I want to build a summary of the last 2 days without scanning every record. > > > > It appears that there is no way to get MySQL to use the index on GMTBase to > > avoid scanning all 8 million rows. I estimate it should only scan about > > 267K rows. Explain mentions the GMTBase index but says it will examine > > about a million rows. That seems to roughly match my estimate of the number > > of distinct GMTBase values. > > > > The query I want to run is: > > > > select RuleID, GMTBase, sum(Credited) > > from RuleHistograms > > where GMTBase > DATE_SUB(CURRENT_DATE, INTERVAL 2 DAY) > > group by GMTBase > > > > Have I done something wrong or is there simply no way to avoid scanning all > > of those records? > > *Something* is wrong. I'm virtually certain I've done this sort of > thing before without having MySQL perform full table scans. I can't > tell what it is off the top of my head, but it should be that bad. > > Jeremy > -- > Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! > <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ > > [book] High Performance MySQL -- http://highperformancemysql.com/ > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
