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]
