Captain Paralytic wrote:
> On 6 Jun, 09:32, Daniele <dankan775removet...@yahoo.it> wrote:
>> Hi All
>> I've started a project connect with geolocation IP adresses
>> I have 2 table, one with all the iprange and one with the location
>> Table block over 2.800.000 row
>> CREATE TABLE `block` (
>> `startIpNum` bigint(15) NOT NULL,
>> `endIpNum` bigint(15) NOT NULL,
>> `locId` int(11) NOT NULL,
>> PRIMARY KEY (`startIpNum`,`endIpNum`)
>> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
>> Field name min value max value min / max lenght
>> startIpNum 33996344 3741315072 8 /10
>> endIpnum 33996351 3741319167 8 /10
>> locId 2 196511 1/6
>>
>> table location 200.000 row
>> CREATE TABLE `location` (
>> `locId` int(9) NOT NULL,
>> `country` text collate utf8_unicode_ci,
>> `region` varchar(50) collate utf8_unicode_ci default NULL,
>> `city` varchar(50) collate utf8_unicode_ci default NULL,
>> `latitude` int(20) NOT NULL,
>> `longitude` int(20) NOT NULL,
>> KEY `locId` (`locId`)
>> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
>>
>> the query I use
>> SELECT country,region,city,latitude,longitude
>> FROM location
>> WHERE locId = (
>> SELECT locId
>> FROM block
>> WHERE startIpNum < INET_ATON( '74.208.9.80' )
>> AND endIpNum >= INET_ATON( '74.208.9.80' )
>> )LiMIT 0,1
>>
>> at the moment on my developing machine I'm getting the result in 0,42
>> sec average.
>> Is there any way to improve it ?
>> The application I'm developing will make request between 50 and 70 ip at
>> time.
>> Also in my case is beater to make a request for each ip or i can pass an
>> array of ip via the query ?
>> The ip they will be different for each request as i will cache the requests
>> Many Thanks
>
> Use a JOIN instead of a sub-select.
I'm new to the join
i'm using this query but is slower (3.5sec ) than the subquery i posted
on top (0.4sec )

SELECT location.country, location.region, location.city,
location.latitude, location.longitude
FROM location
RIGHT JOIN block ON location.locId = block.locId
WHERE block.startIpNum < INET_ATON( '74.208.9.81' )
AND block.endIpNum >= INET_ATON( '74.208.9.81' )
LIMIT 0 , 30

I'm writing it wrong ?
any help ? Direction ?
Thanks