Keith Medcalf, on Monday, January 27, 2020 04:02 AM, wrote... > > > This version generates the most efficient query plan in 3.31.0 when you > have indexes on the necessary columns: > > CREATE INDEX t0_1 on t0 (a, idate, c); -- c does not have to be in the > index > CREATE INDEX t1_1 on t1 (f, idate); > CREATE INDEX t2_1 on t2 (pid, wyear); -- this could be a without rowid > table with both columns in the primary key
I think I already have these INDEXes, but I will make sure. Thanks for this one also. I love having different ways to write the code. > with keys (pid, idate0, idate1) > as ( > select distinct pid, > ( > select max(idate) > from t0 > where a == pid > ), > ( > select max(idate) > from t1 > where f == pid > ) > from ( > select distinct pid > from t2 > ) > ) > SELECT a.a, > a.c, > a.e, > b.g, > b.h, > b.i, > coalesce(( > SELECT 'YES' > FROM t2 > WHERE wYear == a.c > AND pid == a.a > ), 'NO') AS digital > FROM t0 as a, t1 as b, keys > WHERE a.a == keys.pid > AND b.f == keys.pid > AND a.idate == keys.idate0 > AND b.idate == keys.idate1 > AND a.c == 2020 > ; > > without help the query planner does not seem to generate a very good plan > but maybe that is because the sample data is so small ... or maybe it does > and I cannot tell with such small data ... but this forces the query to > execute in the manner I think it should. If you take the "distinct" from > the keys select it frees up the query planner to perhaps find a better plan > -- you need the "select distinct pid from t2" to prevent duplicate rows. Thanks, Keith. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
