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
