2010/1/4 Simon Slavin <slav...@bigfraud.org>: > > On 4 Jan 2010, at 2:38pm, Jeremy Zeiber wrote: > >> That particular query runs in ~ 30 seconds with outerdetail.header or >> header.headerid. I do have another query which gives the same result >> that doesn't quite run as fast as the first, but it is certainly faster >> than the second: >> >> 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; >> >> That runs in about 3 seconds. Still, there are only a few thousand rows >> in the test database, and the real data is going to have hundreds of >> thousands of rows, and this is just a small portion of the query. Is >> there any way to rewrite the query to a better performing one? > > 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 > > Simon. Regards, Simon _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
