Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

> Why don't you just explicitly sort by bar.foo? > > > sqlite> EXPLAIN QUERY PLAN SELECT bar.foo as id, foo.baz FROM bar CROSS JOIN > foo ON bar.foo = foo.id ORDER BY bar.foo LIMIT 10, 10; > 0|0|0|SCAN TABLE bar > 0|1|1|SEARCH TABLE foo USING INTEGER PRIMARY KEY (rowid=?) > sqlite> I have

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

mitris From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of Nico Williams <n...@cryptonector.com> Sent: Thursday, November 17, 2016 4:32 PM To: SQLite mailing list Subject: Re: [sqlite] Query Planner fails to recognise efficient strategy w

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

On Thu, Nov 17, 2016 at 04:48:20PM +0200, Paul wrote: > Replacing JOIN does not help either: > > sqlite> EXPLAIN QUERY PLAN SELECT foo.id FROM bar JOIN foo ON bar.foo = > foo.id ORDER BY id DESC LIMIT 0, 40; > selectidorder fromdetail >

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

On Thu, Nov 17, 2016 at 09:54:01AM -0500, Richard Hipp wrote: > Standard SQL requires an implied NOT NULL on all PRIMARY KEY columns. > But due to a coding error, early versions of SQLite did not enforce > that, and so we have taken care not to enforce it on all subsequent > versions of SQLite to

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

> On 11/17/16, Paul wrote: > > > >> On 11/17/16, Richard Hipp wrote: > >> > On 11/17/16, Paul wrote: > >> >> That's why there was a LEFT JOIN in the first place, but as it seems, > >> >> it > >> >> wasn't that good idea. > >> > > >> > Try using CROSS JOIN instead of just JOIN or LEFT JOIN.

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

ers@mailinglists.sqlite.org> Subject: Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint On 11/17/16, Marc L. Allen <mlal...@outsitenetworks.com> wrote: > I'm not sure that's a valid trick, as bar.foo can be NULL, in which > case the LEFT

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

On 11/17/16, Paul wrote: > >> On 11/17/16, Richard Hipp wrote: >> > On 11/17/16, Paul wrote: >> >> That's why there was a LEFT JOIN in the first place, but as it seems, >> >> it >> >> wasn't that good idea. >> > >> > Try using CROSS JOIN instead of just JOIN or LEFT JOIN. The

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

> On 11/17/16, Richard Hipp wrote: > > On 11/17/16, Paul wrote: > >> That's why there was a LEFT JOIN in the first place, but as it seems, it > >> wasn't that good idea. > > > > Try using CROSS JOIN instead of just JOIN or LEFT JOIN. The query > > planner in SQLite will not reorder a CROSS

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

On 11/17/16, Richard Hipp wrote: > On 11/17/16, Paul wrote: >> That's why there was a LEFT JOIN in the first place, but as it seems, it >> wasn't that good idea. > > Try using CROSS JOIN instead of just JOIN or LEFT JOIN. The query > planner in SQLite will not

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

On 11/17/16, Paul wrote: > That's why there was a LEFT JOIN in the first place, but as it seems, it > wasn't that good idea. Try using CROSS JOIN instead of just JOIN or LEFT JOIN. The query planner in SQLite will not reorder a CROSS JOIN. -- D. Richard Hipp d...@sqlite.org

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

day, November 17, 2016 9:32 AM > To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > Subject: Re: [sqlite] Query Planner fails to recognise efficient strategy > when '=' condition gives a strong hint > > On 11/17/16, Marc L. Allen <mlal...@outsitenetworks.com> wrote:

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

> On 11/17/16, Marc L. Allen wrote: > > Maybe I'm missing something, but... > > > > ORDER BY id > > > > Is ordering by the ID the right-hand side of a LEFT join. As such, it > > depends on how NULL factors into an ORDER BY. If NULL comes first, it has > > to find enough records where the LEFT

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

Of Richard Hipp Sent: Thursday, November 17, 2016 9:32 AM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint On 11/17/16, Marc L. Allen <mlal...@outsitenetworks.c

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

On 11/17/16, Marc L. Allen wrote: > Maybe I'm missing something, but... > > ORDER BY id > > Is ordering by the ID the right-hand side of a LEFT join. As such, it > depends on how NULL factors into an ORDER BY. If NULL comes first, it has > to find enough records

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter Sent: Thursday, November 17, 2016 8:53 AM To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Query Planner fails to recognise efficient st

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

It's not a solution, because in your example, foo will be scanned until limit is reached. This may take considerable amount of time, if results are parse. Also, this solution is totally useless. It's a partial index on 'foo', meaning that I can know true or false having only rows of 'foo'. In

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

Maybe you are looking for SELECT id,baz from foo where exists( select 1 from bar where foo = id) ... which has the effect of easily extending to an arbitrary number of bar tables via additional exists subqueries that may be connected by logical operators -Ursprüngliche Nachricht- Von: