MySQL Forums
Forum List  »  Optimizer & Parser

Re: Large range query stops using indexes
Posted by: KimSeong Loh
Date: June 21, 2007 03:20AM

Correct, it gets the left most node and the right most node and scan everything in between. However, unlees it is the clustered index, it will have to use the pointer or whatever to read the row having that value in the node in the b-tree.

So, there will be a random scan to read the rows that satisfy the range.

If the left-most and right-most are near to the left and right end of the tree, meaning it needs to scan huge percentage of the tree, and then reading the data rows from a different location, it can be slower than a sequential full table scan.

If the index you are using is a Primary key of InnoDB, then the range scan will be better, in fact InnoDB uses B+tree, so it will find the leftmost value, and scan right till the max value, and this should be the fastest.

Otherwise, a range that scan huge percentage of the index tree is likely to be slower in MyISAM or non-primary key in InnoDB

Options: ReplyQuote

Written By
Re: Large range query stops using indexes
June 21, 2007 03:20AM
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.