Would it not be more efficient to say: select 1 from t1 limit 1;
? > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Kirill M?ller > Sent: Thursday, 26 November, 2015 15:03 > To: SQLite mailing list > Subject: Re: [sqlite] Query flattening for left joins involving subqueries > on the right-hand side > > On 26.11.2015 21:12, Clemens Ladisch wrote: > > Kirill M?ller wrote: > >> On 25.11.2015 16:32, Clemens Ladisch wrote: > >>> Kirill M?ller wrote: > >>>> For a left join with a subquery on the right-hand side, that subquery > >>>> doesn't seem to be flattened. > >>> This is rule 3 of <http://www.sqlite.org/optoverview.html#flattening>. > >> I wonder if this rule might be relaxed a bit. > > Only if you relax your requirement that the results must be correct. > > > > > > In the general case, a left outer join can be rewritten like this: > > > > SELECT ... FROM A JOIN B ON ... > > UNION ALL > > SELECT ... FROM A WHERE NOT EXISTS (look up in B) > > > > This query would be more likely to be flattenable, but also be slower. > > > Thanks. Let's not focus on terminology -- I thought "flattening" was the > right word to use, but it probably isn't. Of course I'm looking for > correct results. > > Originally, I attached a script but it seems that it's been stripped. > I've pasted it below. I see no reason why the following two queries (1 > and 3 in the script) can't be executed with the same plans: > > SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN t2 USING (a) LIMIT 1) > SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN (SELECT * FROM t2) zzz2 > USING (a) LIMIT 1) > > This is for two tables t1 and t2 with a single column "a". The script > creates them and populates them with 200000 rows each. > > > -Kirill > > > #!/bin/bash > > db=test.sqlite > > #if false; then > rm -f $db > > n=200000 > > sqlite3 $db "CREATE TABLE t1 (a int primary key)" > seq 1 $n | sqlite3 $db ".import /dev/stdin t1" > > sqlite3 $db "CREATE TABLE t2 (a int primary key)" > seq 1 $n | sqlite3 $db ".import /dev/stdin t2" > #fi > > q() { > sqlite3 $db "EXPLAIN QUERY PLAN $1" > time sqlite3 $db "$1" > } > > q "SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN t2 USING (a) LIMIT 1)" > q "SELECT count(*) FROM (SELECT * FROM (SELECT * FROM t1) zzz1 LEFT JOIN > t2 USING (a) LIMIT 1)" > q "SELECT count(*) FROM (SELECT * FROM t1 LEFT JOIN (SELECT * FROM t2) > zzz2 USING (a) LIMIT 1)" > q "SELECT count(*) FROM (SELECT * FROM t1 INNER JOIN (SELECT * FROM t2) > zzz2 USING (a) LIMIT 1)" > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
