Re: [sqlite] Getting an advance list of RowIDs for a query result set

*I’m now wondering if you omit the WHERE & ORDER BY and run the following EXPLAIN QUERY PLAN SELECT BaseTbl.RowID FROM BaseTbl left join Tbl1 on comparison_1 left join Tbl2 on comparison_2 . . left join Tbln on comparison_n then if it returns more than 1 row then this implies there’s a

Re: [sqlite] Getting an advance list of RowIDs for a query result set

E.Pasma wrote >> 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

Re: [sqlite] Getting an advance list of RowIDs for a query result set

>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. If Tbl_2

Re: [sqlite] Getting an advance list of RowIDs for a query result set

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

Re: [sqlite] Getting an advance list of RowIDs for a query result set

>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

Re: [sqlite] Getting an advance list of RowIDs for a query result set

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,

Re: [sqlite] Getting an advance list of RowIDs for a query result set

>Thanks to you, this topic has inspired a useful change or changes. Wow. Glad I could help. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting an advance list of RowIDs for a query result set

x wrote: From: E.Pasma Sent: 26 November 2017 17:30 To: SQLite mailing list Subject: Re: [sqlite] Getting an advance list of RowIDs for a query result set If step 3 is xxx-ed and only left-joins remain to be

Re: [sqlite] Getting an advance list of RowIDs for a query result set

From: E.Pasma Sent: 26 November 2017 17:30 To: SQLite mailing list Subject: Re: [sqlite] Getting an advance list of RowIDs for a query result set >If step 3 is xxx-ed and only left-joins remain to be considered then >the

Re: [sqlite] Getting an advance list of RowIDs for a query result set

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

Re: [sqlite] Getting an advance list of RowIDs for a query result set

> If it’s joined to the BaseTbl by an integer primary key or FULLY joined by a > unique index then the table is redundant. I’m talking there. If it’s an inner join SQLite needs to check the record exists in the joined table. Sorry about that, back to left joins.

Re: [sqlite] Getting an advance list of RowIDs for a query result set

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

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 --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

Re: [sqlite] Getting an advance list of RowIDs for a query result set

Simon, I’ve no users. I’ve been teaching myself c++ (and SQLite) during a lengthy illness so this is just experimentation for me. This subject touches on a previous question of mine you were involved in regarding redundant tables. The following may jog your memory create table TblA(A

Re: [sqlite] Getting an advance list of RowIDs for a query result set

On 25 Nov 2017, at 1:15pm, curmudgeon wrote: > Given a select where a 'base table' is attached to lookup tables > how can I determine which of the lookup tables can be removed from the table > such that > > select BaseTbl.RowID from ... where ... order by ... > > will

Re: [sqlite] Getting an advance list of RowIDs for a query result set

Sorry, in last post select * from (select Value from carray(ID+?1, ?2, 'int64')) inner join AwfyBigTbl on AwfyBigTbl.RowID = _Value; by setting ?1 = TopRecNo and ?2 = n. should read select * from (select Value from carray(*?1*, ?2, 'int64')) inner join AwfyBigTbl on AwfyBigTbl.RowID = _Value;

Re: [sqlite] Getting an advance list of RowIDs for a query result set

A trivial example of what I'm trying to do. Given select * from AwfyBigTbl where ACol=?; I'd run the query select RowID from AwfyBigTbl where ACol=?; step through the records and store the values in a std::vector called ID. I could then retrieve n records starting at TopRecNo (0 based) with