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