MySQL Forums
Forum List  »  Optimizer & Parser

Re: two column int index not used correctly
Posted by: Jørgen Løland
Date: June 06, 2011 03:15AM

Hi Alexander,

It is almost impossible for MySQL to resolve this kind of query in an efficient way. Basically, MySQL has two options:

* Use range access on the ip_int index
* Table scan

If MySQL would choose to use the index, it would have to look at at least one index entry for each unique ip_start_int value less than or equal to 3566268565. In your case, MySQL evaluates this possibility but concludes that reading all rows from the table sequentially will be quicker than reading that many index entries and then read rows from the table in a random order.

The difference in response time between the queries is not valid to check whether performance would have been better if the index was used because a lot more index entries have to be checked:

Entries in index:                [                                    ]
Index entries matching case 1:   [******************************      ]
Index entries matching case 2:   [*****                               ]

Where
case 1: ip_start_int <= 3566268565
case 2: ip_start_int <= 3568565

A valid comparison would be between these queries:

SELECT country 
FROM static_ip_countries FORCE INDEX ip_int 
WHERE ip_start_int <= 3566268565 AND ip_end_int >= 3566268565

vs

SELECT country 
FROM static_ip_countries 
WHERE ip_start_int <= 3566268565 AND ip_end_int >= 3566268565

If there is no overlap between the ip ranges it is probably better to use the FORCE INDEX query because the index is (most likely) in main memory and MySQL only has to read one row from the table. In that case, I advice you to use the FORCE INDEX variant.

Hope this helps,
Jørgen Løland
Software Engineer, MySQL, Oracle
jorgenloland.blogspot.com

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.