[Sorry for the blast from the past.] I think this class of problem does also happen on fts, there was a thread on August 7 on sqlite-dev about it. Unfortunately, I don't see any open-access web-mirrors of that list to refer to, but here's a members-only ref: http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-dev/2008-August/000330.html
AFAICT, when you have a join where one table has a good index, the virtual table cannot signal that it has an even better index. I could not follow the index-selection logic well enough to have any suggestions how to improve things. In the fts case, this breaks the query because the MATCH operator _only_ works when used to access the index. -scott On Tue, Aug 19, 2008 at 12:31 PM, Hartwig Wiesmann <[EMAIL PROTECTED]> wrote: > > Am 18.08.2008 um 21:32 schrieb Dennis Cote: > >> Thomas Sailer wrote: >>> >>> Interestingly, the original query is extremely compute-bound, there >>> is >>> almost no disk activity! >>> >>> Looking at the output opcodes from the queries, I can't see any >>> significant difference. Though I have to admit I'm by far no expert >>> in >>> vmdb opcodes... >>> >> >> You can use "explain query plan <statement>" to get some clues as to >> what is happening, but the vdbe code shows it best. >> >> The original query is doing a full table scan through all 2.6 million >> records in the main table, and for each record it uses the rtree to >> locate the 20 records inside the rectangle set by your limits, then it >> compares the id of each of these records to see if it matches the id >> of >> the main table record. For each match it dumps all the data in both >> records. >> >> The second query use the rtree index to locate the 20 records in the >> limit rectangle, and then uses the btree index to locate those 20 >> records quickly, and then dumps the data for that record. >> >> HTH >> Dennis Cote >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > Hello, > > I had an identical experience a few days ago and posted it under a > different topic. Because SQLite is not doing well as mentioned in the > example of the documentation I suggest to change the documentation > using the proposed solution. I also had to find it out the hard way > and I think by modifying the documentation there are at least a few > people less who have to go that way. > > BTW: I suppose the same problem occurs when using fts? > > Hartwig > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
