On Sat, Aug 21, 2010 at 10:45 PM, ve3meo <holden_fam...@sympatico.ca> wrote: > "Eric Smith" ... >> I haven't used it myself, but I'm pretty sure this is what the R*tree >> module was designed for: > > I have not used it either but was intrigued by your suggestion. Looking into > it, my sense was that it would be advantageous for a 2-dimension or more > search and I think this was borne out by my experiments, albeit with my very > limited knowledge and experience. I hasten to add that maybe my queries were > not the best designed. > > I created an R-tree virtual table with the minimum number of columns: id, > xmin, xmax and populated it with the rowid from Peng's table A and > position-10 in xmin and position+10 in xmax. Thus any value lying between > xmax and xmin is no more than a distance of 10 from the point pointed to in > Table A by id. Indeed, a simple select on a single value between xmax and > xmin returned rows from the virtual table faster than the correspondingly > simple select on the real table with an index on position (something like > 6ms vs. 10 ms). > > However, on the 10,000 row test table on which I reported earlier that Jim's > 'between' query was fastest at ~2.3s, the best I could get by working an > R-tree virtual table into the mix was ~30s, and that with an index on Name > or Name+Position. Without the index... ~60s, not much better than Peng's > original indexless query and way more complicated. > > I think the penalty is in the extra JOIN required - 3 tables instead of 2 - > with the speed advantage on the 'between' constraint being swamped by the > volume of intermediate rows.
I don't quite understand why there are 3 tables with R-tree. Would you please show me what query you used? Thank you very much. -- Regards, Peng _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
