Hallooo mga master and guru... ask lang ko unsa inyo ma suggest ani na
situation...
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('203.114.138.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('203.114.138.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('203.114.138.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? thanks advance...
_________________________________________________
Kagay-Anon Linux Users' Group (KLUG) Mailing List
[email protected] (http://lists.linux.org.ph/mailman/listinfo/klug)
Searchable Archives: http://archives.free.net.ph
