I have two simple tables - one that defines a statistic, and one that hold
the data for each statistic:
CREATE TABLE Statistic
(
StatID INTEGER PRIMARY KEY,
OwningComputer TEXT NOT NULL
);
CREATE TABLE StatData
(
StatID INTEGER NOT NULL,
Value INTEGER NOT NULL,
Date INTEGER NOT NULL
);
and indices.
CREATE INDEX Ind_StatData_StatID on StatData (StatID, Date);
CREATE INDEX Ind_StatData_Date on StatData (Date);
I'm trying to figure out the best way to find any entries in Statistic that
don't have any corresponding entries in StatData (ie what Statistic is
defined, but no longer has any data attached).
The following query works correctly, but when there is a few 100MB of
StatData entries, it's pretty slow:
SELECT StatID FROM Statistic WHERE StatID NOT IN (SELECT StatID FROM
StatData GROUP BY StatID);
Is there any slick way to make the GROUP BY faster, since I don't really
need to group by all that data? Since there is an index on StatData.StatID,
I would assume the GROUP BY would work by just hitting the index, but I've
been wrong before.
Thanks for any ideas from the group.
Doug
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
