Re: [sqlite] Performance problem for a simple select with range

[Kees Nuyt]

[Default] On Mon, 29 Oct 2007 15:25:18 +0200, "Dani Valevski"
<[EMAIL PROTECTED]> wrote:

>I think I have a performance problem for a simple select with range.
>
>My Tables:
>CREATE TABLE locations(
>                locid    INTEGER PRIMARY KEY,
>                country TEXT,
>                region    TEXT,
>                city    TEXT,
>                postalCode TEXT,
>                latitude REAL,
>                longitude REAL,
>                dmaCode INTEGER,
>                areaCode INTEGER)
>
>CREATE TABLE blocks(
>                startIpNum INTEGER,
>                endIpNum INTEGER,
>                locId INTEGER)
>
>My Data:
>http://www.maxmind.com/app/geolitecity
>Blocks table has 2,776,436 rows
>Locations table has 159,488 rows
>
>After inserting the data I run analyze.
>
>My Query:
>select * from blocks,locations where locations.locid = blocks.locid AND ? >=
>blocks.startIpNum AND ? <= blocks.endIpNum
>(replace ? with a number)
>
>Disclaimer:
>I'm a bit new to databases.
>
>Performance issues:
>I use python's sqlite3 module to run the query.
>With this configuration it takes about 0.6 seconds to complete the query. I
>think this is too slow. I could write a binary tree myself and have searches
>like this take, O(log(num_rows)) which is
>7*something_which_shouldnt_take_too_much. Am I wrong? (see the disclaimer)
>
>Anyway, I thought the problem was that startIpNum, endIpNum are not indexed.
>So I added indices for them (even tried indexing them both). This only makes
>the query take about 3 seconds.
>Ideas anyone?
>
>Source:
>is attached.
>
>Thank you for your help

Just some suggestions:
Index locid in both tables, and rewrite

> select *
>   from blocks,locations 
>  where locations.locid = blocks.locid
>  AND ? >= blocks.startIpNum 
>  AND ? <= blocks.endIpNum

to:

 select *
   from blocks
  INNER JOIN locations USING (locid)
  where ? >= blocks.startIpNum 
    AND ? <= blocks.endIpNum

or:

 select *
   from locations
  INNER JOIN blocks USING (locid)
  where ? >= blocks.startIpNum 
    AND ? <= blocks.endIpNum

HTH
-- 
  (  Kees Nuyt
  )
c[_]

-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------