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

Reply via email to