I wonder if mysql isn't trying to process where GMTBase > DATE_SUB(CURRENT_DATE, INTERVAL 2 DAY)
What about doing this date subtracting in PHP and adding the result to the SQL statement. > 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? > > Thanks, > _M > > > -- > 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]
