Large range query stops using indexes

[Solomon Chang]

I began to notice some disastrous query behavior at work, especially where very large tables are involved. It seems that if I'm performing a range query against aforementioned large table...

mysql> explain select foo, bar FROM fubar WHERE fb_id between 1000 AND 10000;
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | views | ALL | barfoo | NULL | NULL | NULL | 14454750 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)

...then it doesn't select the obvious choice for a key, and it rowscans through the entire table. However, if I chop my range down...

mysql> explain select foo, bar FROM fubar WHERE fb_id between 1000 AND 1452;
+----+-------------+-------+-------+---------------+----------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+---------+-------------+
| 1 | SIMPLE | views | range | barfoo | barfoo | 5 | NULL | 2600144 | Using where |
+----+-------------+-------+-------+---------------+----------+---------+------+---------+-------------+
1 row in set (0.00 sec)

...suddenly everything is all peachy again. (However, increment the range by just one more, and it's suddenly rowscanning again.) My question is, how do I get large range queries to actually use an index?

A promising link to another article in this forum (http://forums.mysql.com/read.php?35,87094,87251) states that the optimizer will sometimes determine that it will take more resources to load the index into memory than to rowscan the entire table, but this does not seem likely, as I'm devoting 6G of index_memory to this server, and the following formula does not seem to come anywhere near to filling index_memory:

453 rows (the critical threshold of examined rows before the optimizer decides to rowscan), times 5-byte integer, times 25-byte index overhead per index entry (?) equals 13590 bytes?!