Re: [sqlite] Poor performance with nested query in outer join

Simon Slavin writes: > I understand that SQLite handles VIEWs as if you had defined and saved > a SELECT statement. So if you JOIN with a VIEW, does SQLite handle it > as as JOIN or a sub-SELECT ? It's just the same as if you JOIN with a sub-SELECT (the query for the

Re: [sqlite] Poor performance with nested query in outer join

On 10 Dec 2011, at 10:35pm, Nick Smallbone wrote: > the difference between my query and your query is > that I have a subquery (select * from b) and you don't. I understand that SQLite handles VIEWs as if you had defined and saved a SELECT statement. So if you JOIN with a VIEW, does SQLite

Re: [sqlite] Poor performance with nested query in outer join

"Black, Michael (IS)" writes: > Natural joins are generally considered to be evil. Too many columns > in common can be bad. > > If you just spell it out it works as expected > > sqlite> explain query plan select * from a left join b where a.id=1 and > b.id=a.id; >

Re: [sqlite] Poor performance with nested query in outer join

Simon Slavin writes: > On 9 Dec 2011, at 10:25pm, Nick Smallbone wrote: > >> select * from a left natural join (select * from b) where id = 1; > > Try not to use sub-selects when you can use a JOIN instead. > Especially don't use them in combination. If you express this

Re: [sqlite] Poor performance with nested query in outer join

Black, Michael (IS) wrote: > sqlite> explain query plan select * from a left join b where a.id=1 and > b.id=a.id; Make it select * from a left join b on b.id=a.id where a.id=1; The join condition must be in the ON clause, otherwise the left join behaves like a plain

Re: [sqlite] Poor performance with nested query in outer join

ion Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org] Sent: Saturday, December 10, 2011 8:24 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Poor performance with nes

Re: [sqlite] Poor performance with nested query in outer join

On 9 Dec 2011, at 10:25pm, Nick Smallbone wrote: > select * from a left natural join (select * from b) where id = 1; Try not to use sub-selects when you can use a JOIN instead. Especially don't use them in combination. If you express this as just a JOIN you'll find that the optimizer

[sqlite] Poor performance with nested query in outer join

Hi list, I have the following schema: CREATE TABLE a(id int primary key); CREATE TABLE b(id int primary key); I want to find information about a particular id, and my query boils down to something like select * from a left natural join (select * from b) where id = 1; (in the real code,