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?