"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. Tom _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
