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 where >>> child=nodes.id); >>> >>> saved in my test about 10% of time >> >> Then I have to doubt your test; the generated code (see the EXPLAIN >> output) is exactly the same. > >
the 3rd step of EXPLAIN changed from: 1|0|0|SCAN TABLE edges to: 1|0|0|SCAN TABLE edges USING COVERING INDEX iedges luuk@opensuse:~/tmp> sqlite3 nodes.db SQLite version 3.8.10.2 2015-05-20 18:17:19 Enter ".help" for usage hints. sqlite> .timer on sqlite> select * from nodes where not exists (select * from edges where child=nodes.id) and id<100000; Run Time: real 254.400 user 254.007998 sys 0.119976 sqlite> select * from nodes where not exists (select * from edges where child=nodes.id) and id<100000; Run Time: real 234.342 user 233.348752 sys 0.491637 sqlite> select * from nodes where not exists (select 1 from edges where child=nodes.id) and id<100000; Run Time: real 219.904 user 218.968920 sys 0.651569 sqlite> select * from nodes where not exists (select 1 from edges where child=nodes.id) and id<100000; Run Time: real 219.929 user 219.562780 sys 0.127948 sqlite> select * from nodes where not exists (select * from edges where child=nodes.id) and id<100000; Run Time: real 236.423 user 234.648774 sys 1.622957 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
