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

Reply via email to