x wrote:
I proceed as follows
1. Omit a table join from the SQL and try preparing it.
2. If it prepares OK then the table isn’t involved in the WHERE or
ORDER BY.
3. If it’s joined to the BaseTbl by an integer primary key or
FULLY joined by a unique index then the table is redundant. By FULLY
I mean ALL fields of the index are included in the join as otherwise
the BaseTbl’s relationship with it is of a ONE TO MANY nature which
means the BaseTbl RowID won’t uniquely identify a row of the
original query. [Two things I’m unsure about are a) how nulls affect
unique index joins and b) how to deal with tables that aren’t
directly linked to the BaseTbl (i.e. they’re linked via an
intermediate table)].
4. If the table is needed reintroduce it into the SQL.
Do this in turn for each of the joins.
If step 3 is xxx-ed and only left-joins remain to be considered then
the SQLite3 engine is likely to fullfill this optimization in its
next release.
I tested this on the current (2017-11-17) pre-release snapshot. As
far as I see any outer joined table may be discarded from the query
plan if only rowid from the base table is selected. Using SELECT
DISTINCT applies this also to one-to-many joins.
I’m wondering if the second of those trunk changes is in any way
related to what I’m trying to do. The above is a bit long winded and
not easy to code so it would be great if the SQLite query optimizer
did it all for me.
It looks relevant, but I did not test that.
To me the time taken to grab a grid page of data is negligible if
you know where to look for it on disc. I tend therefore to time
queries by how fast I can get all the BaseTbl RowIDs into a vector.
The biggest table in my database has 2.4 million rows and yet wait
cursors are a very rare sight.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
