MySQL index on between query

Yesterday engineer pass me a very simple query, 2 tables ip and country
mysql> desc ip;
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| id            | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| address_start | varchar(16) | NO   |     | NULL    |                |
| address_end   | varchar(16) | NO   |     | NULL    |                |
| number_start  | bigint(20)  | NO   |     | NULL    |                |
| number_end    | bigint(20)  | NO   | MUL | NULL    |                |
| country_code  | varchar(3)  | NO   |     | NULL    |                |
| city_id       | bigint(20)  | NO   |     | NULL    |                |
+---------------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
mysql> desc country;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(64) | NO   | UNI | NULL    |       |
| code    | varchar(3)  | NO   | PRI | NULL    |       |
| blocked | char(1)     | NO   |     | Y       |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
MySQL>
 
mysql> select c.* from ip left join country c on c.code=ip.country_code where 3533467147 between ip.number_start and ip.number_end;
+-----------+------+---------+
| name      | code | blocked |
+-----------+------+---------+
| Australia | AU   | Y       |
+-----------+------+---------+
1 row in set (0.86 sec)

ip table totally around 3 millions data, country got 255 rows, MyISAM engine , engineer complain query too slow, I check the indexes on ip and country,  they already create a index i_start_end on column number_start and number_end,

mysql> explain select c.* from ip left join country c on c.code=ip.country_code where 3533467147 between ip.number_start and ip.number_end;
+----+-------------+-------+--------+---------------+------+---------+---------------------+---------+-------------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref                 | rows    | Extra       |
+----+-------------+-------+--------+---------------+------+---------+---------------------+---------+-------------+
|  1 | SIMPLE      | ip    | ALL    | i_start_end   | NULL | NULL    | NULL                | 2952409 | Using where |
|  1 | SIMPLE      | c     | eq_ref | code          | code | 11      | ips.ip.country_code |       1 | NULL        |
+----+-------------+-------+--------+---------------+------+---------+---------------------+---------+-------------+
2 rows in set (0.00 sec)

explain the query, find the key and key_len all equals to null, try to change the index columns sequence from (number_start,number_end) to (number_end,number_start),

mysql> explain select c.* from ip left join country c on c.code=ip.country_code where 3533467147 between ip.number_start and ip.number_end;
+----+-------------+-------+--------+---------------+-------------+---------+---------------------+--------+----------------------------------+
| id | select_type | table | type   | possible_keys | key         | key_len | ref                 | rows   | Extra                            |
+----+-------------+-------+--------+---------------+-------------+---------+---------------------+--------+----------------------------------+
|  1 | SIMPLE      | ip    | range  | i_start_end   | i_start_end | 8       | NULL                | 406428 | Using index condition; Using MRR |
|  1 | SIMPLE      | c     | eq_ref | code          | code        | 11      | ips.ip.country_code |      1 | NULL                             |
+----+-------------+-------+--------+---------------+-------------+---------+---------------------+--------+----------------------------------+
2 rows in set (0.00 sec)

now the query become to use index condition, search only take 0.04 sec.
mysql> select c.* from ip left join country c on c.code=ip.country_code where 3533467147 between ip.number_start and ip.number_end;
+-----------+------+---------+
| name      | code | blocked |
+-----------+------+---------+
| Australia | AU   | Y       |
+-----------+------+---------+
1 row in set (0.04 sec)