On 5 Oct 2010, at 10:43am, Mail wrote: > SELECT COUNT(DISTINCT t0.Z_PK) FROM ZFFFOOD t0 WHERE (t0.ZFOODGROUP = ? AND > (SELECT COUNT(*) FROM ZFFWEIGHT t1 WHERE (t0.Z_PK = t1.ZFOOD) ) > ?)
Did you miss out 'AS' from your FROM clause between ZFFFOOD and t0 ? And again in the sub-SELECT ? Looking at your WHERE clause you have 't0.ZFOODGROUP = ?' and I can see an index on the ZFOODGROUP column, so that should be okay. So I suspect that the time-suck is the sub-SELECT. Can you rephrase the main SELECT so it has the appropriate kind of JOIN instead of a sub-SELECT ? But I think your biggest problem is that COUNT(*) is extremely slow because it can't use indexes. Could you put a field in ZFFFOOD which tells you how many groups that ZFFFOOD has ? I know it raises problems of database integrity but if you need to do this operation a lot it's going to speed it up tremendously. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
