Can anyone suggest a good way to optimize the following query?
SELECT count(*) FROM propositions p, output o WHERE p.verb_id=o.verb_id AND p.tag=o.tag AND (p.stop!=o.stop OR p.start!=o.start);
CREATE INDEX whatever ON output(verb_id,tag);
That will make it O(NlogN) instead of O(N**2).
> > I don't think this will be much help and is very implementation specific, I > expect, but.... Your first two where clauses are effectively a "JOIN USING > verb_id,tag", which has a much better chance of using any built in > optimizations for cross indexing. >
Actually, SQLite implements JOIN USING by translating the USING clausing into some extra WHERE clause terms. It does the same with NATURAL JOIN and JOIN ON. So while those constructs might be helpful to the human reader, they don't really make any difference to SQLite's query optimizer.
The index should solve your problem.
-- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
