Consider query below; SELECT key FROM t1 LEFT JOIN ( SELECT key,max(rev),data FROM t2 WHERE rev < ? GROUP BY key ) USING (key) ORDER BY key ? LIMIT ?
In above query sqlite will materialize the t2-sub-query and then start working on the outer query. I have a lot of data in t2 so this will consume a lot of time. To overcome this I perform above query manually i two stages; I fetch the t1 data, then for each row I do a lookup and manually join. This is very fast by but makes it hard to reuse this base-query in other queries. So my question is; Can sqlite do lookup-type joins, like to do manually in code, to avoid the overhead of materializing the full t2-query on all keys, and using just a fraction of this work? I suspect the answer is no, if so, maybe this is solvable through a custom virtual table? I have looked at ext/misc/eval.c, and this custom function could be used if a function where alloed to return multiple columns.. Fredrik _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
