Op 27 nov 2017, om 20:51 heeft x het volgende geschreven:
So if I build a view that includes look-ups in other tables, the
optimizer may skip these at places where not selected. However only
if
the look-ups are written as outer joins. Then it may be good practice
allways doing that. For instance:
create view vtrack as
select trackname, artistname
from track
left join artist ON trackartist=artistid -- note left join
;
~
~
Yeah, I was thinking always to use left joins when it doesn’t make
any difference. I’m using a C++ wrapper for SQLite I wrote myself.
It contains a QueryGrid type that automatically splits the SQL into
RowSQL and ColSQL as described earlier. It is still possible to
build a vector of BaseTbl.RowIDs when the original query contains
inner joins provided all columns of the unique index used in the
join are satisfied (see ***Example). If it’s unable to do that (due
to 1 to many joins) it computes ‘select count(*) from .....’ and
sets ColSQL to ‘select ColList from ... limit ?1, ?2’ so that it
still fits in with the ‘fetch a range of data’ modus operandi and
also allows a record counter and vertical scrollbar positioning.
***Example
select ColList
from BaseTbl
left join Tbl_1 on Tbl_1.X = BaseTbl.X
Inner join Tbl_2 on Tbl2.Y = BaseTbl.Y
left join Tbl_3 on Tbl3.Z = BaseTbl.Z
where BaseTbl.Col=?
Provided Tbl_2 has unique index on Y the RowID list could be had from
Select BaseTbl.RowID
from BaseTbl
left join Tbl_1 on Tbl_1.X = BaseTbl.X
Inner join Tbl_2 on Tbl2.Y = BaseTbl.Y
left join Tbl_3 on Tbl3.Z = BaseTbl.Z
where BaseTbl.Col=?
which the SQLite query optimiser would presumably reduce to
Select BaseTbl.RowID
from BaseTbl
Inner join Tbl_2 on Tbl2.Y = BaseTbl.Y
where BaseTbl.Col=?
I’ve still to write code to check the query contains no joins that
are ‘1 to many’. I don’t suppose there’s an easy way of determining
this from explain or explain query plan? At the moment all i can
think of is to scrape the index name from the explain query plan
Detail column, check the number of variables involved (i.e. number
of ? marks) and compare with the number of columns in the index.
What about changing the remaining inner join to left join
Select BaseTbl.RowID
from BaseTbl
left join Tbl_2 on Tbl2.Y = BaseTbl.Y
where BaseTbl.Col=?
and see if the SQLiter optimizer now leaves Tbl_2 out from the query
plan. It will only do that if it is not a 1-to-n join.
But that leaves you with the change that the join is 1-to-0 so to say.
I understood that was a show stopper.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
