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]
