[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] -----------------------------------------------------------------------------