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 groups/categories, foods themselves and nutrition information. The database schema: CREATE TABLE ZFFFOOD ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZFOODGROUP INTEGER, ZSHORTDESC VARCHAR, ZLONGDESC VARCHAR ); CREATE TABLE ZFFFOODGROUP ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZLONGDESC VARCHAR, ZICON VARCHAR ); CREATE TABLE ZFFFOODNUTRIENT ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZFOOD INTEGER, ZNUTRIENT INTEGER, ZAMOUNTINHUNDREDGRAM FLOAT ); CREATE TABLE ZFFNUTRIENT ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZTAGNAME VARCHAR, ZUNITS VARCHAR, ZLONGDESC VARCHAR ); CREATE TABLE ZFFWEIGHT ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZFOOD INTEGER, ZGRAMWEIGHT FLOAT, ZAMOUNT FLOAT, ZLONGDESC VARCHAR ); CREATE TABLE Z_METADATA (Z_VERSION INTEGER PRIMARY KEY, Z_UUID VARCHAR(255), Z_PLIST BLOB); CREATE TABLE Z_PRIMARYKEY (Z_ENT INTEGER PRIMARY KEY, Z_NAME VARCHAR, Z_SUPER INTEGER, Z_MAX INTEGER); CREATE INDEX ZFFFOODNUTRIENT_ZAMOUNTINHUNDREDGRAM_INDEX ON ZFFFOODNUTRIENT (ZAMOUNTINHUNDREDGRAM); CREATE INDEX ZFFFOODNUTRIENT_ZFOOD_INDEX ON ZFFFOODNUTRIENT (ZFOOD); CREATE INDEX ZFFFOODNUTRIENT_ZNUTRIENT_INDEX ON ZFFFOODNUTRIENT (ZNUTRIENT); CREATE INDEX ZFFFOOD_ZFOODGROUP_INDEX ON ZFFFOOD (ZFOODGROUP); CREATE INDEX ZFFFOOD_ZLONGDESC_INDEX ON ZFFFOOD (ZLONGDESC); CREATE INDEX ZFFNUTRIENT_ZTAGNAME_INDEX ON ZFFNUTRIENT (ZTAGNAME); CREATE INDEX ZFFWEIGHT_ZFOOD_INDEX ON ZFFWEIGHT (ZFOOD); CREATE INDEX ZFFWEIGHT_ZLONGDESC_INDEX ON ZFFWEIGHT (ZLONGDESC); 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 ZFFWEIGHT t1 WHERE (t0.Z_PK = t1.ZFOOD) ) > ?) 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. I dropped the inner select - disregarding the wrong count as this is just a speed test - and the query is a bit faster, but still too slow (0.0314 seconds): SELECT COUNT(DISTINCT t0.Z_PK) FROM ZFFFOOD t0 WHERE t0.ZFOODGROUP = ? Due to the size of the database, I put it online for download rather than attaching it. ;-) Please download a copy here: http://dl.dropbox.com/u/8867517/food.sqlite.zip Any hints are welcome. I'm keen to learn. :-) Cheers, Sven _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
