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