Re: Large range query stops using indexes

[KimSeong Loh]

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