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 how to compute it". Is this an exception to the rule where the query planner must be told how to compute the result? On 1 January 2018 at 10:58, Clemens Ladisch <clem...@ladisch.de> wrote: > 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 works but is very slow when there are a million nodes and edges. > > I thought an index on edges(child) might help > > but it didn't > > The index has the wrong affinity; the edges.child column must be integer. > > The following query would be simpler, and does not need the index (because > SQLite > always creates a temporary index for the lookup anyway): > > select * from nodes where id not in (select child from edges); > > > Regards, > Clemens > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
