Re: inner join on range criteria: unable to use index?

[KimSeong Loh]

Correct, no table scanning necessary

but if you also have
...
3 1
3 2
3 3
3 4
4 1
4 2
4 3
4 4
and many more bigger values

You will need to go further down to get 3 1 , 3 2 and then to 4 and so on.

2 issues here, if you are searching a huge percentage of the data from the index, this can actually be slower than doing a full table scan, due to random IO and sequential IO.

2nd issue, the composite index (A,B) here does not help much when you have a range search on the earlier column A. Maybe only help to reduce the data access that is not required when the B condition fail.