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
