> > Would you care to repeat those two SELECTs, but after making indices on the > X and Y columns ? >
Simon, Tim, forgot to mention, there were also two indexes in the test db, on X and on Y. Without them 1,8 seconds and 1/10 data flow would not be possible )) On Mon, Mar 22, 2010 at 2:52 PM, Tim Romano <tim.rom...@yahoo.com> wrote: > Another addendum: apologies <red-face emoticon> -- I hope my > discussion was clear enough despite the disconnect between my head and > my fingers; I just noticed that I had typed "INNER JOIN" (yikes) rather > than "INNER LOOP", by which I mean fetching the rowids using an index > (on LATITUDE say) and then having to loop through those rowids in order > to fetch rows from the base table to compare the LONGITUDE. > I think you got better results for loop because of some specific case with your real data. With two dimensional data and without using R-Tree I doubt we can get rid of either looping or pre-sorting. I just guess that in general Select intersect select should be better because sqlite quickly finds two ranges, sort results, and performs one-pass synced scan, while your approach seeks for every candidate from scratch. Also there are cases when Select intersect select will be always better, for example when together with your coordinates you will have large additional data, like descriptions. Without indexes quering [Select Y From table Where rowid = ] will also read extra data from every record even if you don't need it, but if you have tow compact indexes indexes only by X and Y, and use intersect the data flow will be the same regardless of your record size. In my test case, if I'd added descriptions to the table and every description were 1000 bytes, the final size would grow from 44M to 1G, but the query still read only 3M of data from the table. Max _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
