"Peng Yu" ... >> 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.
For every row in table A1, look up in the R-Tree table A_X those rows whose xmin and xmax contain the position value, then look up in table A2 the rowids corresponding to A_X.id and discard those whose name does not match A1.name and those whose position is the same. 3 tables, 2 JOINs. SELECT A1.Name, A1.position, A_X.id, A_X.xmin, A_X.xmax, A2.Name, A2.position FROM A AS A1, A_X, A AS A2 WHERE A1.position != A2.position and A1.Name = A2.Name and A2.id = A_X.id and A1.position between A_X.xmin and A_X.xmax LIMIT 400000; Tom _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
