MySQL Forums
Forum List  »  Optimizer & Parser

Why filtering, ordering and limiting does not use index?
Posted by: Marc Canaleta
Date: March 30, 2010 07:26AM

I have a table with more than one milion rows with an index not unique in a column. When I try to get first N rows less than a value (combination of WHERE column < constant ORDER BY column LIMIT n), it uses the index but rows_examined is very high. It should be N...:


mysql> explain SELECT score_alltimePoints FROM score WHERE score_alltimePoints < 4915 ORDER BY score_alltimePoints DESC LIMIT 30;
+----+-------------+-------+-------+---------------------+---------------------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------------+---------------------+---------+------+--------+--------------------------+
| 1 | SIMPLE | score | range | score_alltimePoints | score_alltimePoints | 4 | NULL | 768417 | Using where; Using index |
+----+-------------+-------+-------+---------------------+---------------------+---------+------+--------+--------------------------+
1 row in set (0.09 sec)



It seems obvious for me that it should use the index (btree) to get to the first row and browse descending through the index. It should only examine N rows.

Is this possible?

Thank you!

Options: ReplyQuote


Subject
Views
Written By
Posted
Why filtering, ordering and limiting does not use index?
2993
March 30, 2010 07:26AM


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.