List:General Discussion« Previous MessageNext Message »
From:Mathieu Bruneau Date:March 25 2007 12:22am
Subject:Re: Index not used sometimes -- why?
View as plain text  
M5 a écrit :
> Sometimes, when I run a query on a table, the index will be used. But if
> I run the SAME query, but on a larger set of rows (e.g., where id
> BETWEEN 1 AND 100000 vs WHERE id BETWEEN 1 AND 500), the index is not
> used. (EXPLAIN tells me this.) And as a result, the query runs slow.
> 
> The question I have is, why is the index not used on larger-range
> queries? Could it be because the key_buffer_size parameter is too small?
> Presently, it's set at the default value of 8M. Could increasing that
> value to, say, 128M, potentially solve this problem? Or is there some
> other factor involved?
> 
> Thanks.
> 
> ...Rene
> 
> --MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
> http://lists.mysql.com/mysql?unsub=1
> 
> 
Hi,
	As far as I know the key_buffer doesn't affect this... It's a decision
that the optimiser take at some point. I think that when he detects that
he would need to scan more than 60% of the index, he decide that that
would be too much read for nothing and that a full scan may be better.
This is generally a wise decision but in some case it's not (because you
don't really scan 60%, or because all the row you wanna read are
sequential or ...)

To be sure, he has the best information to take his decision, make sure
your table is analyze, especially if you do many delete in your table!
You could force him to use the index but beware of that solution as it
may have an impact if you dataset change with time (growth)

Regards,
-- 
Mathieu Bruneau
aka ROunofF

===
GPG keys available @ http://rounoff.darktech.org
Thread
Index not used sometimes -- why?M524 Mar
  • Re: Index not used sometimes -- why?Mathieu Bruneau25 Mar