On 2018/01/04 9:28 PM, Dinu wrote:
Hi all,
I've ran into an optimisation problem with a double-left join that works as
an "either" clause.
The query is as follows:
SELECT *
FROM
a
LEFT JOIN
b ON <cond>
LEFT JOIN
c ON <cond>
WHERE
b.someId IN (1,2,3) OR
c.someId IN (4,5)
This results in a bloated execution plan:
SEARCH a
SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX
SEARCH SUBQUERY 2 USING AUTOMATIC COVERING INDEX
However, the semantically equivalent:
SELECT *
FROM
a
LEFT JOIN
b ON <cond> AND b.someId IN (1,2,3)
LEFT JOIN
c ON <cond>AND c.someId IN (4,5)
WHERE
b.someId IS NOT NULL OR
c.someId IS NOT NULL
Gets the proper execution plan:
SEARCH b
SEARCH c
EXECUTE LIST SUBQUERY
These Queries are not equivalent, they cannot and should not have the
same query plan.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
