Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

Luuk wrote: >> Clemens Ladisch wrote: >>> Luuk wrote: On 01-01-18 03:14, Shane Dev wrote: > select * from nodes where not exists (select * from edges where > child=nodes.id); Changing this to: select * from nodes where not exists (select 1 from edges where

Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

On 01-01-18 16:52, E.Pasma wrote: > Clemens Ladisch wrote: > >> Luuk wrote: >>> On 01-01-18 03:14, Shane Dev wrote: select * from nodes where not exists (select * from edges where child=nodes.id); >>> >>> Changing this to: >>> >>> select * from nodes where not exists (select 1 from edges

Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

Clemens Ladisch wrote: Luuk wrote: On 01-01-18 03:14, Shane Dev wrote: select * from nodes where not exists (select * from edges where child=nodes.id); Changing this to: select * from nodes where not exists (select 1 from edges where child=nodes.id); saved in my test about 10% of time

Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

Shane Dev wrote: > Apart from visual inspection and testing, is there anyway to be sure your > query selects the same results as my query? Can I interest you in things like relational algebra or tuple calculus? ;-) >>> select * from nodes where not exists (select * from edges where >>>

Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

Shane Dev wrote: Hi Clemens, Your query is much faster on my system - thanks! Apart from visual inspection and testing, is there anyway to be sure your query selects the same results as my query? From https://sqlite.org/queryplanner.html "When programming in SQL you tell the system what

Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

Luuk wrote: > On 01-01-18 03:14, Shane Dev wrote: >> select * from nodes where not exists (select * from edges where >> child=nodes.id); > > Changing this to: > > select * from nodes where not exists (select 1 from edges where > child=nodes.id); > > saved in my test about 10% of time Then I

Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

Hi Clemens, Your query is much faster on my system - thanks! Apart from visual inspection and testing, is there anyway to be sure your query selects the same results as my query? From https://sqlite.org/queryplanner.html "When programming in SQL you tell the system what you want to compute, not

Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

On 01-01-18 12:18, Luuk wrote: > On 01-01-18 03:14, Shane Dev wrote: >> Hello, >> >> I have a directed acyclic graph defined as follows - >> >> sqlite> .sch >> CREATE TABLE nodes(id integer primary key, description text); >> CREATE TABLE edges(parent not null references nodes, child not null >>

Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

On 01-01-18 03:14, Shane Dev wrote: > Hello, > > I have a directed acyclic graph defined as follows - > > sqlite> .sch > CREATE TABLE nodes(id integer primary key, description text); > CREATE TABLE edges(parent not null references nodes, child not null > references nodes, primary key(parent,

Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

Shane Dev wrote: > CREATE TABLE nodes(id integer primary key, description text); > CREATE TABLE edges(parent not null references nodes, child not null > references nodes, primary key(parent, child)); > > select * from nodes where not exists (select * from edges where > child=nodes.id); > > This

Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

This query will use the index you proposed. SELECT * FROM nodes NATURAL JOIN (SELECT parent AS id FROM edges WHERE parent NOT IN (SELECT child FROM edges)); Peter On Sun, Dec 31, 2017 at 6:14 PM, Shane Dev wrote: > Hello, > > I have a directed acyclic graph defined as