Quoth Mail <sv...@gmx.net>, on 2010-10-05 11:43:29 +0200:
> SELECT COUNT(DISTINCT t0.Z_PK) FROM ZFFFOOD t0 WHERE (t0.ZFOODGROUP
> = ? AND (SELECT COUNT(*) FROM ZFFWEIGHT t1 WHERE (t0.Z_PK =
> t1.ZFOOD) ) > ?)
There should not need to be a DISTINCT when talking about a primary
key. They will already be distinct by definition, no? (I'm assuming
your primary keys will not be null, even though you don't have a NOT
NULL constraint on the column.)
Aside from that, if you want "at least one", EXISTS is the obvious
operator to use rather than first requesting the count (and having to
iterate all the rows even though only the first one is needed to
demonstrate existence.)
> My current task is to get the number of foods that belong to each
> group and have at least one weight data related to them.
That suggests something like:
SELECT g.Z_PK AS "group", COUNT(f.Z_PK) AS "count"
FROM ZFFFOODGROUP g
INNER JOIN ZFFFOOD f ON f.ZFOODGROUP = g.Z_PK
WHERE EXISTS (SELECT w.Z_PK FROM ZFFWEIGHT w WHERE w.ZFOOD = f.Z_PK)
GROUP BY g.Z_PK;
(I think the WHERE EXISTS could be done with another join, but it
would probably be more convoluted.)
> When inserting 12 for ZFOODGROUP and 0 for count (I never check for
> another count value here, it's always 0), I get a result of 761
> which takes 0.0591 seconds. As I have to fetch the counts of all
> available groups, this query is sent in a loop which easily takes
> several seconds to finish.
Do the loop in the query itself using GROUP BY instead, if you can,
rather than issuing several queries.
My local, unrigorous measurements suggest that on my machine, with the
provided example database, your original query takes 40 ms of CPU per
run and the above takes 16 ms total; I imagine the use of GROUP BY and
EXISTS and the lack of the extra DISTINCT are the primary factors, but
I haven't checked thoroughly enough to say so confidently. I'm using
SQLite 3.7.2 on Debian GNU/Linux sid AMD64.
---> Drake Wilson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
