Re: Range query on datetime with index - any optimization?
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]
Range query on datetime with index - any optimization?
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]
Re: Range query on datetime with index - any optimization?
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]
Re: Range query on datetime with index - any optimization?
At 01:30 PM 5/5/2004, Daniel Clark wrote: 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. Nice try - but it's not the problem. Replacing the equation with a constant does not change the number of rows that will be reviewed. For example, explain SELECT * FROM `RuleHistogram` where GMTBase '2004-05-03' +---+---+---+-+-+++-+ | table | type | possible_keys | key | key_len | ref| rows | Extra | +---+---+---+-+-+++-+ | RuleHistogram | range | GMTBase | GMTBase | 8 | [NULL] | 954388 | Using where | +---+---+---+-+-+++-+ Anyway, I figured it out. Sorry of the confusion - I think my math is wrong and that's just the number of records it takes to go through 2 days. I'll look for another solution. Thanks, _M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
