MySQL Forums
Forum List  »  Optimizer & Parser

Large range query stops using indexes
Posted by: Solomon Chang
Date: June 11, 2007 10:43PM

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?!

Options: ReplyQuote


Subject
Views
Written By
Posted
Large range query stops using indexes
6428
June 11, 2007 10:43PM
2692
February 28, 2009 04:49AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.