For someone who doesn't read C, could someone who knows please describe the SQLite INTERSECT algorithm? What optimizations are available to it? Does INTERSECT have to assume that neither vector is pre-sorted? Here's the background of my question:
For latitude/longitude queries, I was speculating that after a table had reached some unknown number of rows, INTERSECTing two vectors of rowids might be faster than an INNER JOIN in which only one index had been chosen by SQLite, the one on LAT or LON, which would require reading the base table in order to fetch the other geo-values. The count of matching rowids extracted via the index is the number of rows that would have to be read from the base table in order to examine the other geo-column's value. I supposed that with an INTERSECT, one could select the rowids of those tuples that met the latitude criteria, and INTERSECT these with the rowids of those tuples that met the longitude criteria, so that each select could use an index. However, the INNER JOIN with a single index was always faster in my tests, though I never tested with more than a million rows. So it seems that intersecting two vectors of rowids is more expensive than using one index and reading the base table to get the other geo-value, either that, or the point at which INTERSECT becomes faster than INNER JOIN is well beyond the size of my test database. Regards Tim Romano _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
