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 <[email protected]>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 <[email protected]>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 <[email protected]>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 <[email protected]> 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 <[email protected]> wrote:
>>>>
>>>>> oops, ignore. mysql man diay.
>>>>>
>>>>>
>>>>> On Tue, Feb 5, 2013 at 6:33 PM, botp <[email protected]> wrote:
>>>>>
>>>>>> On Tue, Feb 5, 2013 at 1:20 PM, Camilo III Lozano <
>>>>>> [email protected]> 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
>>>> [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
>>>
>>>
>>>
>>>
>>>
>>
>>
>> --
>> ==================
>> 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
>
--
==================
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
[email protected] (http://lists.linux.org.ph/mailman/listinfo/klug)
Searchable Archives: http://archives.free.net.ph
