mysql> select inet_ntoa(‘2130706433‘),inet_aton(‘127.0.0.1‘); +-------------------------+------------------------+ | inet_ntoa(‘2130706433‘) | inet_aton(‘127.0.0.1‘) | +-------------------------+------------------------+ | 127.0.0.1 | 2130706433 | +-------------------------+------------------------+ 1 row in set (0.00 sec)

mysql ----5.6.13

mysql> show create table test \G;*************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `ip_from` int(10) unsigned DEFAULT NULL, `ip_to` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_ip` (`ip_from`,`ip_to`), KEY `idx_ip_from` (`ip_from`) ) ENGINE=InnoDB AUTO_INCREMENT=9568111 DEFAULT CHARSET=utf8 COLLATE=utf8_bin 1 row in set (0.01 sec)ERROR: No query specified------------------------------------------------------mysql> show index from test;+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| test | 0 | PRIMARY | 1 | id | A | 9289578 | NULL | NULL | | BTREE | | | | test | 1 | idx_ip | 1 | ip_from | A | 9289578 | NULL | NULL | YES | BTREE | | || test | 1 | idx_ip | 2 | ip_to | A | 9289578 | NULL | NULL | YES | BTREE | | | | test | 1 | idx_ip_from | 1 | ip_from | A | 9289578 | NULL | NULL | YES | BTREE | | |+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+4 rows in set (0.00 sec) mysql> select count(*) from test; +----------+ | count(*) | +----------+ | 9541210 | +----------+ 1 row in set (2.84 sec)

mysql> explain SELECT * FROM test WHERE ip_from<=2352356 AND ip_to>=2352356;+----+-------------+-------+-------+--------------------+--------+---------+------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+--------------------+--------+---------+------+------+-----------------------+| 1 | SIMPLE | test | range | idx_ip,idx_ip_from | idx_ip | 5 | NULL | 1 | Using index condition |+----+-------------+-------+-------+--------------------+--------+---------+------+------+-----------------------+1 row in set (0.08 sec)

mysql> EXPLAIN SELECT * FROM test WHERE id IN (-> SELECT id FROM test WHERE ip_from<=2352356 AND ip_to>=2352356 ); +----+-------------+-------+--------+----------------------------+---------+---------+---------------------+------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+--------+----------------------------+---------+---------+---------------------+------+--------------------------+| 1 | SIMPLE | test | range | PRIMARY,idx_ip,idx_ip_from | idx_ip | 5 | NULL | 1 | Using where; Using index | | 1 | SIMPLE | test | eq_ref | PRIMARY | PRIMARY | 8 | ip2location.test.id | 1 | NULL |+----+-------------+-------+--------+----------------------------+---------+---------+---------------------+------+--------------------------+2 rows in set (0.01 sec)mysql> status; -------------- mysql Ver 14.14 Distrib 5.5.35, for debian-linux-gnu (x86_64) using readline 6.2Connection id: 4305567 Current database: ip2location Current user: ip2location@10.1.255.10 SSL: Not in use Current pager: stdout Using outfile: ‘‘ Using delimiter: ; Server version: 5.6.13-log MySQL Community Server (GPL) Protocol version: 10 Connection: ip2location.cgs2bjzqxcxl.us-east-1.rds.amazonaws.com via TCP/IP Insert id: 1 Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 30 days 18 hours 51 min 44 secThreads: 4 Questions: 21017670 Slow queries: 4 Opens: 188007 Flush tables: 1 Open tables: 147 Queries per second avg: 7.901--------------------------------------------- mysql> EXPLAIN SELECT * FROM test WHERE id IN ( -> SELECT id FROM test WHERE ip_from<=2352356 AND ip_to>=2352356 ); +----+--------------------+-------+-----------------+----------------------------+---------+---------+------+--------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+--------------------+-------+-----------------+----------------------------+---------+---------+------+--------+-------------+| 1 | PRIMARY | test | ALL | NULL | NULL | NULL | NULL | 206509 | Using where || 2 | DEPENDENT SUBQUERY | test | unique_subquery | PRIMARY,idx_ip,idx_ip_from | PRIMARY | 8 | func | 1 | Using where |+----+--------------------+-------+-----------------+----------------------------+---------+---------+------+--------+-------------+2 rows in set (0.00 sec)mysql> status; -------------- mysql Ver 14.14 Distrib 5.5.37, for Linux (x86_64) using EditLine wrapperConnection id: 5 Current database: howe Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: ‘‘ Using delimiter: ; Server version: 5.5.37-log Source distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /home/mysql/mysql5/tmp/mysql.sock Uptime: 6 min 52 secThreads: 3 Questions: 208 Slow queries: 0 Opens: 112 Flush tables: 1 Open tables: 105 Queries per second avg: 0.504--------------

mysql> explain SELECT * FROM test WHERE ip_from<=2352356 ORDER BY ip_from DESC LIMIT 1;+----+-------------+-------+-------+--------------------+--------+---------+------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+--------------------+--------+---------+------+------+-----------------------+| 1 | SIMPLE | test | range | idx_ip,idx_ip_from | idx_ip | 5 | NULL | 1 | Using index condition |+----+-------------+-------+-------+--------------------+--------+---------+------+------+-----------------------+1 row in set (0.00 sec)

mysql> explain SELECT * FROM test WHERE ip_from<=2352356 ORDER BY ip_from DESC LIMIT 1;+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+| 1 | SIMPLE | test | range | idx_ip_from | idx_ip_from | 5 | NULL | 1 | Using index condition |+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+1 row in set (0.00 sec)