Re: [sqlite] INTERSECT optimization, is it possible?

On Mon, Jan 11, 2010 at 12:56 AM, D. Richard Hipp wrote: > > On Jan 10, 2010, at 4:50 AM, Max Vlasov wrote: > > > Documentation says that INTERSECT implemented with temporary tables > > either > > in memory or on disk. Is it always the case? > > No. > > If there is an ORDER BY

Re: [sqlite] INTERSECT optimization, is it possible?

On Jan 10, 2010, at 4:50 AM, Max Vlasov wrote: > Documentation says that INTERSECT implemented with temporary tables > either > in memory or on disk. Is it always the case? No. If there is an ORDER BY clause, SQLite may run each subquery as a separate co-routine and merge the results. If

Re: [sqlite] INTERSECT optimization, is it possible?

Considering that INTERSECT is logically nothing but a special case of relational join (the exact opposite of cartesian product), where all columns are involved in the join condition, you should just be able to reuse any optimizations that exist for join, including primary/unique keys/etc. --

[sqlite] INTERSECT optimization, is it possible?

Documentation says that INTERSECT implemented with temporary tables either in memory or on disk. Is it always the case? The problem is that if I have several selects (six for example) when each produces thousands of results and the intersection is only hundreds the query takes about minute to