ahh, i should explain what is explain is. it should tell you if you need to index a column on your table in this case the city. run the command without indexing the city column then run it again with the filed indexed. check out the rows each time each run, if there is an obvious decrease in rows with the index on then the field must be indexed.
http://dev.mysql.com/doc/refman/5.0/en/using-explain.html -- On Tue, Feb 5, 2013 at 3:55 PM, Camilo III Lozano <[email protected]>wrote: > 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 >
_________________________________________________ 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
