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
