Re: [klug] make query faster...

Top Page

Reply to this message
Author: Camilo III Lozano
Date:  
To: Kagay-Anon Linux Users' Group (KLUG) Mailing List
Subject: Re: [klug] make query faster...
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!!!


==============================================
On Wed, Feb 6, 2013 at 12:47 PM, Alfredo Sanchez Jr <tildemark@???>wrote:

> ahh, sample lang to. it does mean na mao na gyud to imong e index, its
> just there to illustrate unsay iyang gamit.
>
> -
>
>
> On Tue, Feb 5, 2013 at 8:27 PM, Camilo III Lozano <camilo3rd@???>wrote:
>
>> @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...
>>
>> =====================
>>
>>
>> On Wed, Feb 6, 2013 at 1:25 AM, Camilo III Lozano <camilo3rd@???>wrote:
>>
>>> ako gi research ang spatial... naa pa sya... murag di man maapply.. or
>>> di lang ko kabalo cguro. hahahaha...
>>>
>>> ===============
>>>
>>> On Wed, Feb 6, 2013 at 12:01 AM, botp <botpena@???> wrote:
>>>
>>>> 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
>>>>
>>>>
>>>>
>>>> On Tue, Feb 5, 2013 at 6:37 PM, botp <botpena@???> wrote:
>>>>
>>>>> oops, ignore. mysql man diay.
>>>>>
>>>>>
>>>>> On Tue, Feb 5, 2013 at 6:33 PM, botp <botpena@???> wrote:
>>>>>
>>>>>> On Tue, Feb 5, 2013 at 1:20 PM, Camilo III Lozano <
>>>>>> camilo3rd@???> 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
>>>>>>
>>>>>>
>>>>>
>>>>
>>>> _________________________________________________
>>>> Kagay-Anon Linux Users' Group (KLUG) Mailing List
>>>> klug@??? (
>>>> 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
>>>
>>>
>>>
>>>
>>>
>>
>>
>> --
>> ==================
>> 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
>> klug@??? (http://lists.linux.org.ph/mailman/listinfo/klug)
>> Searchable Archives: http://archives.free.net.ph
>>
>
>
> _________________________________________________
> Kagay-Anon Linux Users' Group (KLUG) Mailing List
> klug@??? (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://tracker.isourcery.com -- experimental
- http://www.larn.me -- cease to develop
- http://www.seofart.com


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