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

lanner fails to recognise efficient strategy when '=' condition gives a strong hint These are the queries: CREATE TABLE foo( idINTEGER, baz INTEGER, PRIMARY KEY(id) ); CREATE TABLE bar( foo INTEGER, PRIMARY KEY(foo), FOREIGN KEY(foo) REFERENCES foo(id) ON DE

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

> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im > Auftrag von Paul > Gesendet: Donnerstag, 17. November 2016 13:58 > An: General Discussion of SQLite Database > Betreff: [sqlite] Query Planner fails to recognise efficient strategy when > '=' condition give

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

: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Paul Gesendet: Donnerstag, 17. November 2016 13:58 An: General Discussion of SQLite Database <sqlite-users@mailinglists.sqlite.org> Betreff: [sqlite] Query Planner fails to recognise efficient strateg

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

These are the queries: CREATE TABLE foo( idINTEGER, baz INTEGER, PRIMARY KEY(id) ); CREATE TABLE bar( foo INTEGER, PRIMARY KEY(foo), FOREIGN KEY(foo) REFERENCES foo(id) ON DELETE CASCADE ); EXPLAIN QUERY PLAN SELECT foo.id, baz FROM bar LEFT JOIN foo ON