Re: [sqlite] INTERSECT versus INNER JOIN with latitude, longitude queries

2010-03-22 Thread Max Vlasov
> > 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

Re: [sqlite] INTERSECT versus INNER JOIN with latitude, longitude queries

2010-03-22 Thread Tim Romano
Another addendum: apologies -- 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 l

Re: [sqlite] INTERSECT versus INNER JOIN with latitude, longitude queries

2010-03-22 Thread Tim Romano
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 >>> perfo

Re: [sqlite] INTERSECT versus INNER JOIN with latitude, longitude queries

2010-03-22 Thread Tim Romano
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 lon

Re: [sqlite] INTERSECT versus INNER JOIN with latitude, longitude queries

2010-03-22 Thread Simon Slavin
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() % 5) +

Re: [sqlite] INTERSECT versus INNER JOIN with latitude, longitude queries

2010-03-21 Thread Max Vlasov
> > 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.

Re: [sqlite] INTERSECT versus INNER JOIN with latitude, longitude queries

2010-03-21 Thread Tim Romano
On 3/21/2010 5:22 PM, Max Vlasov wrote: > On Sun, Mar 21, 2010 at 3:50 PM, Tim Romano wrote: > > >> For someone who doesn't read C, could someone who knows please describe >> the SQLite INTERSECT algorithm? What optimizations are available to it? >> Does INTERSECT have to assume that neither

Re: [sqlite] INTERSECT versus INNER JOIN with latitude, longitude queries

2010-03-21 Thread Max Vlasov
On Sun, Mar 21, 2010 at 3:50 PM, Tim Romano wrote: > For someone who doesn't read C, could someone who knows please describe > the SQLite INTERSECT algorithm? What optimizations are available to it? > Does INTERSECT have to assume that neither vector is pre-sorted? Here's > the background of my

Re: [sqlite] INTERSECT versus INNER JOIN with latitude, longitude queries

2010-03-21 Thread Tim Romano
On 3/21/2010 10:26 AM, Igor Tandetnik wrote: > Tim Romano wrote: > >> For latitude/longitude queries >> > Without diving into the details of your situation, I wonder if you are aware > of R-Tree extension: > > http://www.sqlite.org/rtree.html > Thank you Igor. I had read about R-TRE

Re: [sqlite] INTERSECT versus INNER JOIN with latitude, longitude queries

2010-03-21 Thread Igor Tandetnik
Tim Romano wrote: > For latitude/longitude queries Without diving into the details of your situation, I wonder if you are aware of R-Tree extension: http://www.sqlite.org/rtree.html -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite