On 6 Jun, 11:49, Daniele <dankan775removet...@yahoo.it> wrote:
> 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 read about join is not advice on big table as it will create a
> instance of the 2 table in one
> but I will try
> thanks- Hide quoted text -
>
> - Show quoted text -

WHAT???

Please could you point me to where you read that?