Re: [sqlite] query performance question

On 5 Oct 2010, at 1:14pm, Igor Tandetnik wrote: > Simon Slavin wrote: >> 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) ) > ?)

Re: [sqlite] query performance question

Simon Slavin wrote: > 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

Re: [sqlite] query performance question

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 ?

Re: [sqlite] query performance question

Thank you very much! Your query is exactly what I was looking for. If I understand you correctly, the main bottleneck is the count in the inner select - is that correct? (disregarding the overall loop for all groups) Am 05.10.2010 um 12:24 schrieb Drake Wilson: > Quoth Mail ,

Re: [sqlite] query performance question

Mail wrote: > 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. > > The query I am using for this is: > SELECT COUNT(DISTINCT t0.Z_PK) FROM ZFFFOOD t0 WHERE (t0.ZFOODGROUP = ? AND > (SELECT COUNT(*) FROM

Re: [sqlite] query performance question

Quoth Drake Wilson , on 2010-10-05 03:24:01 -0700: > > 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" >

Re: [sqlite] query performance question

Quoth Mail , 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

[sqlite] query performance question

Hi everybody, after reading some parts of the documentation and numerous Google searches, I still have no solution for fixing my slow query. I would really appreciate if you could point me in the right direction. The basis is a food database (~38 megabyte total size) that contains