Thanks Keith. This one
Update the omit-table-from-left-join optimization so that it can omit tables from the middle of the join as well as the end. Deals with the case I mentioned in my last post but this one is also interesting Fix a problem preventing the planner from identifying scans that visit at most one row in cases where that property is guaranteed by a unique, not-null, non-IPK column that is the leftmost in its table The way I was going to tackle the problem of redundant tables was as follows. Given SELECT ColA, ColB, ColC, .... FROM BaseTbl jointype_1 Tbl1 on comparison_1 jointype_2 Tbl2 on comparison_2 . . jointype_n Tbln on comparison_n WHERE .... ORDER BY .... I want to find the redundant tables in the following query SELECT BaseTbl.RowID FROM BaseTbl jointype_1 Tbl1 on comparison_1 jointype_2 Tbl2 on comparison_2 . . jointype_n Tbln on comparison_n WHERE .... ORDER BY .... 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. 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. 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. Tom From: Keith Medcalf<mailto:kmedc...@dessus.com> Sent: 25 November 2017 18:15 To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Getting an advance list of RowIDs for a query result set >This is fixed in the current head of trunk. Although the implementation may >change, it will appear in the next release. https://www.sqlite.org/src/timeline?n=50 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
