> > Assuming a table where Latitude column and Longitude column each have > their own index: > > perform select #1 which returns the rowids of rows whose latitude meets > criteria > INTERSECT > perform select #2 which returns the rowids of rows whose longitude meets > criteria > >
Ok, just test. Created a base with a table CREATE TABLE [TestTable] ([Id] INTEGER PRIMARY KEY AUTOINCREMENT, [X] INTEGER,[Y] INTEGER) Filled with 1,000,000 records: INSERT INTO TestTable (X, Y) VALUES ((random() % 50000) + 40000, (random() % 50000) + 40000) Final size: 44M Simple query SELECT rowid FROM TestTable WHERE (X BETWEEN 30000 AND 40000) Time: 330 ms, 110,000 rows Intersect query SELECT rowid FROM TestTable WHERE (X BETWEEN 30000 AND 40000) INTERSECT SELECT rowid FROM TestTable WHERE (Y BETWEEN 30000 AND 40000) Time:1800 ms, 10,000 rows and from my vfs stat the latter reads about 3M of data from this 44M base (so no full table scan) You say that your INNER JOIN QUERY faster? You probably have a different scheme, maybe that's the reason, but please let us know in this case Max _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
