# Curious apparent index lookup failure.  Can anyone explain this unexpected behavior?

**URL:** <https://forums.percona.com/t/curious-apparent-index-lookup-failure-can-anyone-explain-this-unexpected-behavior/5070>\
**Category:** Percona Server for MySQL 5.6\
**Created:** [September 7, 2016, 11:16am UTC](https://forums.percona.com/t/curious-apparent-index-lookup-failure-can-anyone-explain-this-unexpected-behavior/5070 "2016-09-07T11:16:52Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![unixronin](https://avatars.discourse-cdn.com/v4/letter/u/8491ac/32.png) [@unixronin](https://forums.percona.com/u/unixronin)\
**Post date:** [September 7, 2016, 11:16am UTC](https://forums.percona.com/t/curious-apparent-index-lookup-failure-can-anyone-explain-this-unexpected-behavior/5070/1 "2016-09-07T11:16:52Z")

</div>

I have run across what appears to be a strange indexing failure, and a rather counter-intuitive workaround for it. This is in Percona Server 5.6.29-76.2-log, amd64 platform, running on RHEL6.7.

Consider the following table definition:

CREATE TABLE `ip2location` (  
`ip_from` int(10) unsigned zerofill NOT NULL DEFAULT ‘0000000000’,  
`ip_to` int(10) unsigned zerofill NOT NULL DEFAULT ‘0000000000’,  
`country_code` char(2) NOT NULL DEFAULT ‘’,  
`country_name` varchar(255) NOT NULL DEFAULT ’ ',  
PRIMARY KEY (`ip_from`,`ip_to`)  
)

This table contains 378,261 rows. Each ip\_from and ip\_to value appears in the table exactly once, as the first and last address in INET\_ATON form of a specific IP range assigned to a particular country. So not only is (ip\_from, ip\_to) unique, but ip\_from and ip\_to are themselves also unique. ip\_from and ip\_to both increase strictly monotonically, ip\_to is always strictly greater than ip\_from, and there are no overlapping ranges. Any IP address will fall between the ip\_from and ip\_to fields of exactly one row.

Now, we do a lookup of an IP in this table as follows:

SELECT COUNT(\*) FROM ip2location WHERE ip\_from \<= INET\_ATON(‘65.78.23.18’) AND ip\_to \>= INET\_ATON(‘65.78.23.18’);

This should and does return a single row. It should also need to SCAN only a single row. INET\_ATON(‘65.78.23.18’) resolves to 1095636754, and there is exactly one row in which ip\_from \<= 1095636754 \<= ip\_to. That is this row:

mysql\> SELECT \* FROM ip2location WHERE ip\_from \<= INET\_ATON(‘65.78.23.18’) AND ip\_to \>= INET\_ATON(‘65.78.23.18’)\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
ip\_from: 1095628288  
ip\_to: 1096224919  
country\_code: US  
country\_name: United States  
1 row in set (0.04 sec)

MySQL should be able use the index to resolve both WHERE conditions. However, let’s look at the execution plan:

mysql\> EXPLAIN SELECT \* FROM ip2location WHERE ip\_from \<= INET\_ATON(‘65.78.23.18’) AND ip\_to \>= INET\_ATON(‘65.78.23.18’)\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
id: 1  
select\_type: SIMPLE  
table: ip2location  
type: range  
possible\_keys: PRIMARY  
key: PRIMARY  
key\_len: 4  
ref: NULL  
rows: 151312  
Extra: Using where  
1 row in set (0.00 sec)

We can also write the query this way, with the exact same execution plan and results:

mysql\> SELECT \* FROM ip2location WHERE INET\_ATON(‘65.78.23.18’) BETWEEN ip\_from AND ip\_to\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
ip\_from: 1095628288  
ip\_to: 1096224919  
country\_code: US  
country\_name: United States  
1 row in set (0.04 sec)

mysql\> EXPLAIN SELECT \* FROM ip2location WHERE INET\_ATON(‘65.78.23.18’) BETWEEN ip\_from AND ip\_to\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
id: 1  
select\_type: SIMPLE  
table: ip2location  
type: range  
possible\_keys: PRIMARY  
key: PRIMARY  
key\_len: 4  
ref: NULL  
rows: 151312  
Extra: Using where  
1 row in set (0.00 sec)

Whichever way we choose to write the query, we should be able to resolve this to a single row from the index. However, mysqld is scanning 151,000 rows. Why is this?

Here’s a clue:

mysql\> EXPLAIN SELECT \* FROM ip2location WHERE ip\_from \<= INET\_ATON(‘65.78.23.18’)\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
id: 1  
select\_type: SIMPLE  
table: ip2location  
type: range  
possible\_keys: PRIMARY  
key: PRIMARY  
key\_len: 4  
ref: NULL  
rows: 151312  
Extra: Using where  
1 row in set (0.00 sec)

That’s exactly the same number of rows. Apparently mysqld is looking up the first WHERE clause on ip\_from against the primary key and getting 151,312 possible rows. But it is then scanning those 151,312 rows for the ip\_to limit, instead of now checking the second WHERE clause against the same index to narrow it down to a single row.

I was able to devise the following somewhat counter-intuitive workaround, which exploits the query optimizer and a direct index lookup to actually get BETTER performance by adding a subquery in which the subquery’s table lookup is optimized out:

mysql\> EXPLAIN SELECT \* FROM ip2location WHERE ip\_from = (select max(ip\_from) FROM ip2location WHERE ip\_from \<= INET\_ATON(‘65.78.23.18’))\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
id: 1  
select\_type: PRIMARY  
table: ip2location  
type: ref  
possible\_keys: PRIMARY  
key: PRIMARY  
key\_len: 4  
ref: const  
rows: 1  
Extra: Using where  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
id: 2  
select\_type: SUBQUERY  
table: NULL  
type: NULL  
possible\_keys: NULL  
key: NULL  
key\_len: NULL  
ref: NULL  
rows: NULL  
Extra: Select tables optimized away  
2 rows in set (0.00 sec)

This resolves the query with only a single-row table lookup. But it should _already_ be only needing to look up a single row, without having to use this slightly cryptic workaround.

Can anyone explain to me why the index is only resolving the ip\_from condition in the “normal” query, instead of both the ip\_from and ip\_to conditions? Is it because the conditions are expressed as \<= and \>=?