Can you please run ANALYZE then try the plans again ?
This was just after running ANALYZE :)
the fact that they produce the exact same answer is simply by virtue of your WHERE clause being specifically that
Of course, I understand, that's what I've meant - the plan shouldn't differ for this specific query with this specific condition...
Why would you use left join for that? Maybe this is just a really small extract from the real query you are doing and the real one would shed more light?
This is an autogenerated query, the conditions are user-specified, so I must analyze them to determine whether to use INNER or LEFT join in each case if SQLite does not do that. In fact that's what I've implemented at once in my fork of bugzilla (bugzilla4intranet) to optimise for SQLite, and in most cases it works.
But I think SQLite could do this kind of optimisation better inside itself, and most (if not all) client-server DBMSes (even MySQL) actually do it - their plans don't differ for the same query.
Even if NULLs are not specifically excluded, the column schema might include "NOT NULL" for that column which may also incur the optimisation
No-no, even if a column is NOT NULL in an outer-joined table, it may still be null in the result if no matching row is found.
So the condition is simple: turn outer join into an inner if the WHERE clause excludes NULLs.
-- With best regards, Vitaliy Filippov _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
