*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 1
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 not
>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 isn’t
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 inst
>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
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
>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
__
x wrote:
From: E.Pasma<mailto:pasm...@concepts.nl>
Sent: 26 November 2017 17:30
To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Getting an advance list of RowIDs for a query
result set
If step 3 is xxx-ed and only left-joins
From: E.Pasma<mailto:pasm...@concepts.nl>
Sent: 26 November 2017 17:30
To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org>
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 consi
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.
> 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.
__
ite-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
olume.
>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of x
>Sent: Saturday, 25 November, 2017 10:08
>To: SQLite mailing list
>Subject: Re: [sqlite] Getting an advance list of RowIDs for a query
>result set
>
>Simon, I
: Saturday, November 25, 2017 1:26:00 PM
To: SQLite mailing list
Subject: 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 whi
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 find the set of records
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;
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 the
17 matches
Mail list logo
