camilord - 5:03 am on Feb 5, 2013 (gmt 0)

Hi.. need help making my query faster... please help..

my table geoip_ip_blocks has 2M+ rows and geoip_locations has 290k+ rows...

here's my queries...


mysql> SELECT b.id,l.city,l.latitude,l.longtitude FROM geoip_ip_blocks AS b, geoip_locations AS l WHERE b.locID = l.locID AND INET_ATON('113.114.118.94') BETWEEN b.startIpNum AND b.endIpNum;
+---------+------------------+----------+------------+
| id | city | latitude | longtitude |
+---------+------------------+----------+------------+
| 1859525 | Palmerston North | -40.3500 | 175.6167 |
+---------+------------------+----------+------------+
1 row in set (2.40 sec)

mysql>
mysql> SELECT b.id,l.city,l.latitude,l.longtitude FROM geoip_ip_blocks AS b INNER JOIN geoip_locations AS l ON b.locID = l.locID WHERE INET_ATON('113.114.118.94') BETWEEN b.startIpNum AND b.endIpNum;
+---------+------------------+----------+------------+
| id | city | latitude | longtitude |
+---------+------------------+----------+------------+
| 1859525 | Palmerston North | -40.3500 | 175.6167 |
+---------+------------------+----------+------------+
1 row in set (1.58 sec)

mysql> explain SELECT b.id,l.city,l.latitude,l.longtitude FROM geoip_ip_blocks AS b INNER JOIN geoip_locations AS l ON b.locID = l.locID WHERE INET_ATON('113.114.118.94') BETWEEN b.startIpNum AND b.endIpNum;
+----+-------------+-------+--------+---------------------+---------+---------+------------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------+---------+---------+------------------+---------+-------------+
| 1 | SIMPLE | b | ALL | startIpNum,endIpNum | NULL | NULL | NULL | 2106604 | Using where |
| 1 | SIMPLE | l | eq_ref | PRIMARY | PRIMARY | 8 | vtracker.b.locID | 1 | |
+----+-------------+-------+--------+---------------------+---------+---------+------------------+---------+-------------+
2 rows in set (0.16 sec)

mysql>



any suggestion to make it faster?