Hi, Thanks for the help; that makes sense I think you guys are right. Is it worth tuning such a thing? It seems to me like it would be much faster to use the index?
Thanks again Dave > Dave schrieb: >> Hi all, >> I've been trying to optimize some of our queries against a large >> database >> and come up against an index problem I haven't been able to find any >> documentation on. I've cut the query down to the bare minimum, and found >> the following -- >> >> explain Select iname,domain,serv,time from log where date between >> '2008-05-10' and '2008-05-30'; >> +----+-------------+-------+-------+---------------+------+---------+------+ >> -------+-------------+ >> | id | select_type | table | type | possible_keys | key | key_len | >> ref >> | rows | Extra | >> +----+-------------+-------+-------+---------------+------+---------+------+ >> -------+-------------+ >> | 1 | SIMPLE | log | range | date | date | 4 | >> NULL >> | 45178 | Using where | >> +----+-------------+-------+-------+---------------+------+---------+------+ >> -------+-------------+ >> >> >> As you can see in the above query, it uses type "range" and the key date >> is used. If I change it to -05-01 to -05-30 though it does not : >> >> explain Select iname,domain,serv,time from log where date between >> '2008-05-01' and '2008-05-30'; >> +----+-------------+-------+------+---------------+------+---------+------+- >> -------+-------------+ >> | id | select_type | table | type | possible_keys | key | key_len | ref >> | rows | Extra | >> +----+-------------+-------+------+---------------+------+---------+------+- >> -------+-------------+ >> | 1 | SIMPLE | log | ALL | date | NULL | NULL | >> NULL >> | 353558 | Using where | >> +----+-------------+-------+------+---------------+------+---------+------+- >> -------+-------------+ >> >> As you can see the type is now "ALL" and it doesn't work. > > as Ananda already wrote, it seems rows valid for '2008-05-01' to > '2008-05-30' are exceed the threshold when MySQL thinks it is faster to > scan > the table instead of scan the index and than read the table > > >> What could be the cause of this? It seems like its limited to a specific >> number of rows? The rows in explain appear to be wrong... >> mysql> Select count(id) from log where date between '2008-05-01' and >> '2008-05-30'; >> +-----------+ >> | count(id) | >> +-----------+ >> | 85232 | >> +-----------+ >> 1 row in set (0.97 sec) > > "rows" is how many rows MySQL thinks it must examine to execute the query, > not the number of rows possible returned > > http://dev.mysql.com/doc/refman/5.1/en/using-explain.html > > > -- > Sebastian Mendel > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
