Simon Davies wrote: > 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 > Yes, creating an index on detail(data) does speed the query. It is still pretty slow when there are tens of thousands of records in the database. > >> Simon. >> > > Regards, > Simon > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
