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
