are these columns indexed? also, have you tried explain? as in EXPLAIN SELECT * FROM geoip_locations where city = ‘Palmerston North’
will give an idea how many records mysql had to process before getting the data. otherwise i don't see anymore changes in your statement to make it faster besides upgrading your hardware. -- On Tue, Feb 5, 2013 at 1:20 PM, Camilo III Lozano <[email protected]>wrote: > 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 >
_________________________________________________ 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
