On 22 Mar 2010, at 6:15am, Max Vlasov wrote: > 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)
Would you care to repeat those two SELECTs, but after making indices on the X and Y columns ? Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
