On 3/22/2010 7:32 AM, Tim Romano wrote: > On 3/22/2010 2:15 AM, Max Vlasov wrote: > >>> 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 >> >> > Max, > The three main differences between my table schema and yours above: > -- x and y are declared as floats > -- and the x and y values were not random values > > What is your performance with a query that uses only a single index > without the INTERSECT function? > > select rowid from TT where x>= ? and y<= ? and and x<= ? and y>= ? > > Regards > Tim Romano > _______________________________________________ > I forgot to add the third difference: the indexes :-)
TR _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
