This makes a huge difference in speed thanks
>>Also, you can write the query a bit more compactly: >>select fileref from rtable where vsc > 0 and isgraphic = 1 and md5 not in >>(select md5 from rtable where isgraphic = 1 and vsc = 0); On 31 January 2013 19:54, Igor Tandetnik <i...@tandetnik.org> wrote: > On 1/31/2013 2:33 PM, Paul Sanderson wrote: > >> My query is >> >> select fileref from rtable as r where vsc > 0 and isgraphic = 1 and not >> exists (select md5 fr >> om rtable as r1 where r.md5 = r1.md5 and isgraphic = 1 and vsc = 0); >> >> explain query plan and explain have been run on the table with the results >> below. Any ideas where and how I can improve performance? >> > > An index on rtable(md5) should help. If you do have one, and it doesn't > get picked, try changing "isgraphic = 1" to "+isgraphic = 1" in the inner > select (the unary plus operator makes the index on isgraphic inapplicable, > hopefully steering the query planner towards a more helpful index). > > Also, you can write the query a bit more compactly: > > select fileref from rtable where vsc > 0 and isgraphic = 1 and md5 not in > (select md5 from rtable where isgraphic = 1 and vsc = 0); > > -- > Igor Tandetnik > > > ______________________________**_________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users> > -- Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
