"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, ...); > > CREATE TABLE blocks( > startIpNum INTEGER, > endIpNum INTEGER, > locId INTEGER) > > My Data: > Blocks table has 2,776,436 rows > Locations table has 159,488 rows > > My Query: > select * from blocks,locations where locations.locid = blocks.locid AND ? >= > blocks.startIpNum AND ? <= blocks.endIpNum > (replace ? with a number) >
To do searches of this kind with maximum efficiency, you normally want to use a 1-dimensional R-Tree index. SQLite does not support RTree indices natively, though it is conceivable that you could write a RTree virtual table extension for SQLite. Without an RTree index, and unless you can exploit the distribution of data in the blocks table, you really cannot do much better than a full table scan on blocks with an indexed lookup of locations for each matching block. That is probably what is happening on your original query before you added indices. -- D. Richard Hipp <[EMAIL PROTECTED]> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
