MySQL Forums
Forum List  »  Optimizer & Parser

Re: two column int index not used correctly
Posted by: Jørgen Løland
Date: June 17, 2011 01:20AM

Hi Ming,

You'll have to differentiate between ref-access and range-access.

In case 1) MySQL does ref-access, which means that MySQL will read a row from A and do an index lookup in idx_start_ip using the ip value it got from the row in A. This is only possible if the comparison operator is = or <=>. So, MySQL will read each row in A and for each row it will read exactly the rows in ip_range_table that has this ip.

In case 2) you have a range condition using a constant for comparison. When you compare an indexed column to a constant using one of these comparison operators <>, >, >=, <, <=, IS NULL, BETWEEN, or IN(), MySQL starts up the range access machinery. Range may still not be the best access method, so range is not necessarily used, but MySQL will calculate the cost of using it. With range access, MySQL will read exactly the rows with start_ip >= 10000000000. Range and ref access are two completely different beasts.

In case 3) you have a range condition but the indexed column is not compared to a constant. Range access is not applicable when you don't compare to constants. That's why range isn't considered in this case. In this query, MySQL will read all rows from A and for each row all rows from ip_range_table will be read.

Hope this helps,
Jørgen Løland
Software Engineer, MySQL, Oracle

Options: ReplyQuote

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.