i add index locID, startIpNum and endIpNum in geoip_ip_blocks... mysql> EXPLAIN SELECT * FROM geoip_locations where city = 'Palmerston > North'; > > +----+-------------+-----------------+------+---------------+------+---------+------+--------+-------------+ > | id | select_type | table | type | possible_keys | key | > key_len | ref | rows | Extra | > > +----+-------------+-----------------+------+---------------+------+---------+------+--------+-------------+ > | 1 | SIMPLE | geoip_locations | ALL | NULL | NULL | NULL > | NULL | 399254 | Using where | > > +----+-------------+-----------------+------+---------------+------+---------+------+--------+-------------+ > 1 row in set (0.00 sec)
my server is in hostgator... 16cores... 32gb.. =========================== On Tue, Feb 5, 2013 at 7:24 PM, Alfredo Sanchez Jr <[email protected]>wrote: > 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 > -- ================== NZ Mobile: +64 22 159 5396 NZ Telephone: 04 977 9378 PH Mobile #: +63916-3338326 PH Telephone #: (+63)(63) 221-1122 *Personal Projects* - http://www.prendstah.com - http://www.kagayan.com - http://www.zabyer.org - http://www.omarket.co.nz - http://www.larn.me - http://www.seofart.com -- Registered Linux User: #439468
_________________________________________________ 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
