On 4 Jan 2010, at 3:02pm, Simon Davies wrote: > 2010/1/4 Simon Slavin <slav...@bigfraud.org>: >> >> On 4 Jan 2010, at 2:38pm, Jeremy Zeiber wrote: >> >>> SELECT headerid, >>> (SELECT (SELECT COUNT(DISTINCT data) FROM detail WHERE >>> headerid=header.headerid)-COUNT(DISTINCT one.data) FROM detail AS one >>> INNER JOIN detail AS two ON one.data=two.data WHERE >>> two.headerid<one.headerid AND one.headerid=header.headerid) AS newcount >>> FROM header GROUP BY headerid; >> >> Do you have the appropriate indexes defined ? I see lots of matching and >> WHERE clauses and your query may not be finding an index that can do all >> that work for it. > > indeed: > create index i on detail( data ); > > seems to improve performance
But you're also matching on headerid and using the same field in GROUP BY. I can't get my head around your data structure but if the correct indexes to define aren't clear I think you should experiment with defining indexes in data and headerid both as separate indexes and as combination indexes in each order. Once you've done that, see if the SELECT gets faster. If it does, you can figure out which index it uses and delete the others. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
