Daniele 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'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

As Luuk has pointed out. The query is only one part of optimising a database
applicaiton. The tables and indexes must be carefully and correctly designed
as well. You have to use your knowledge and expertise to design a system
that is capable of good performance.

I tend to stick with LEFT JOIN and JOIN as I find the resulting queries
easier to read. Any JOIN condition needs to be indexed correctly in order to
obtain the best results. I added an index to a table built by someone and
the time for a particular JOIN query dropped from 30 seconds to a fraction
of a second. Improvements like that are not that common, but thinking about
what the RDBMS has to do to obtain your required result and building the
tables and indexes to help it do this should be part of any design process.

Daniele <dankan775removethis@yahoo.it> writes:

> 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 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 ?

I think you will see a noticable improvement if you change
your varchar fields to just char. It should allow for more efficient
indexing of the data in those rows (since they will be of fixed length).
Make sure you have indexes created on the criteria used in the query.

Also, and not sure on this, if this is pretty much a read-only
type application you may see a speed improvement by trying MyISAM over
InnoDB tables. May be worth a try.

Hope this helps.

--
John
__________________________________________________ _________________
John Murtari Software Workshop Inc.
jmurtari@following domain 315.635-1968(x-211) "TheBook.Com" (TM)
http://thebook.com/