[klug] make query faster...

[Camilo III Lozano]

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...

[Alfredo Sanchez Jr]

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. 

--

_________________________________________________
Kagay-Anon Linux Users' Group (KLUG) Mailing List
kl...@lists.linux.org.ph (http://lists.linux.org.ph/mailman/listinfo/klug)
Searchable Archives: http://archives.free.net.ph

[Camilo III Lozano]

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..

===========================

--
==================
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

[Alfredo Sanchez Jr]

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

--

[botp]

On Tue, Feb 5, 2013 at 1:20 PM, Camilo III Lozano <cami...@gmail.com> wrote:

any suggestion to make it faster? thanks advance...

the "between"ess is in question. definitely, you'd need a spatial index.

best regards -botp

[botp]

oops, ignore. mysql man diay.

[botp]

oops again.

1  didnt know there is spatial indexing in mysql.. until now..

2  do not know mysql here, so shutting my mouth now : - )

kind regards -botp

[Camilo III Lozano]

ako gi research ang spatial... naa pa sya... murag di man maapply.. or di lang ko kabalo cguro. hahahaha...

===============

_________________________________________________
Kagay-Anon Linux Users' Group (KLUG) Mailing List
kl...@lists.linux.org.ph (http://lists.linux.org.ph/mailman/listinfo/klug)
Searchable Archives: http://archives.free.net.ph

[Camilo III Lozano]

@alfredo: hmmm.. di man necessary ang city sa query.. ang need kay ang startIpNum and endIpNum ... so ako gi index ang duwa... but mo take gihapon ug almost a second or more...

=====================

[botp]

On Tue, Feb 5, 2013 at 8:25 PM, Camilo III Lozano <cami...@gmail.com> wrote:

ako gi research ang spatial... naa pa sya... murag di man maapply.. or di lang ko kabalo cguro. hahahaha...

well, some theory: indexes uses b-trees wc does exact search. however, betweeness scans ranges, ergo standard sql optimizer (no matter how you index)  do not apply.  you may need at least an r-tree wc spatial index provides.

non-theory: testingi lang.

my take is: walay "di lang ko kabalo" for camilo the hacker..

in the meantime, try also using the query cache para ka tabang gamay...

best regards -botp

ps:  testing experience only on postgresql and postgis. so you'll have to test it on your side for mysql...

[Paul Michael Labis]

Camilord, sql will be slow in the long run. My recommendation is use caching technology. The idea is first-time will be slow even with all the indexing, second time will be a lot more faster due to caching...

Regards,

Paul

_________________________________________________
Kagay-Anon Linux Users' Group (KLUG) Mailing List
kl...@lists.linux.org.ph (http://lists.linux.org.ph/mailman/listinfo/klug)
Searchable Archives: http://archives.free.net.ph

--

Paul Labis

Senior Software Engineer

www.paullabis.com | www.feedmoko.com

If there is a will, there is a way!

Privileged/Confidential Information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply email. Please advise immediately if you or your employer does not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of my firm shall be understood as neither given nor endorsed by it.

[Alfredo Sanchez Jr]

ahh, sample lang to. it does mean na mao na gyud to imong e index, its just there to illustrate unsay iyang gamit. 

-

[Camilo III Lozano]

WOW! THANK YOU BOTP... 

Ikaw na akong bag-ong ginoo.. you solved my problem.. SPATIAL INDEX works like a charm...

OUTPUT BEFORE: 

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)

OUTPUT AFTER SPATIAL INDEX IMPLEMENTED:

mysql> SELECT geoip_blocks.locID, geoip_blocks.startIpNum, geoip_blocks.endIpNum

    ->  FROM geoip_blocks INNER JOIN geoip_locations ON geoip_blocks.locID = geoip_locations.locID

    ->          WHERE MBRCONTAINS(ip_poly, POINTFROMWKB(POINT(INET_ATON('203.114.138.94'), 0)));

+--------+------------+------------+

| locID  | startIpNum | endIpNum   |

+--------+------------+------------+

| 199902 | 3413280768 | 3413283071 |

+--------+------------+------------+

1 row in set (0.19 sec)

mysql> SELECT geoip_blocks.locID, geoip_locations.city, geoip_blocks.startIpNum, geoip_blocks.endIpNum

    ->  FROM geoip_blocks INNER JOIN geoip_locations ON geoip_blocks.locID = geoip_locations.locID

    ->          WHERE MBRCONTAINS(ip_poly, POINTFROMWKB(POINT(INET_ATON('203.114.138.94'), 0)));

+--------+------------------+------------+------------+

| locID  | city             | startIpNum | endIpNum   |

+--------+------------------+------------+------------+

| 199902 | Palmerston North | 3413280768 | 3413283071 |

+--------+------------------+------------+------------+

1 row in set (0.00 sec)

mysql> SELECT geoip_blocks.locID, geoip_locations.city,

    ->                      geoip_locations.latitude, geoip_locations.longtitude

    ->                     FROM geoip_blocks INNER JOIN geoip_locations ON geoip_blocks.locID = geoip_locations.locID

    ->                         WHERE MBRCONTAINS(ip_poly, POINTFROMWKB(POINT(INET_ATON('203.114.138.94'), 0)))

    -> ;

+--------+------------------+----------+------------+

| locID  | city             | latitude | longtitude |

+--------+------------------+----------+------------+

| 199902 | Palmerston North | -40.3500 |   175.6167 |

+--------+------------------+----------+------------+

1 row in set (0.00 sec)

thank you again...

@paul_labis: master paul.. wala na nako gi apply imo suggestion kay feeling nako dili jud gihapon sya mo work kay mismo sa PHP nag caching nako and didn't work well.. anyway, thanks gihapon sa info...

@alfredo: thanks gihapon sa imo pre.. :)

SPATIAL INDEX Implemented at Project Visitor Tracker: http://www.tracker.isourcery.com/api/view/MZ59967690GV --- paspas na mo load.. hahahaha... weeeeeeeeeeeeeeeeeehhh!!!

==============================================

• http://tracker.isourcery.com -- experimental
• http://www.larn.me -- cease to develop
• http://www.seofart.com