On 16-10-16 12:00, Cecil Westerhof wrote:
I have defined the following table:
CREATE TABLE messages (
date TEXT NOT NULL DEFAULT CURRENT_DATE,
time TEXT NOT NULL DEFAULT CURRENT_TIME,
type TEXT NOT NULL,
message TEXT NOT NULL,
PRIMARY KEY (date, time, type)
);
CREATE INDEX messages_date ON messages(date);
CREATE INDEX messages_time ON messages(time);
CREATE INDEX messages_type ON messages(type);
And the following view:
CREATE VIEW temperatureStatistics AS
SELECT date AS Date
, MIN(message) AS Minimum
, MAX(message) AS Maximum
, AVG(message) AS Average
, COUNT(*) AS Count
FROM messages
WHERE type = 'cpu-temp'
GROUP BY date
I also created the folowing two queries:
SELECT date AS Date
, MIN(message) AS Minimum
, MAX(message) AS Maximum
, AVG(message) AS Average
, COUNT(*) AS Count
FROM messages
WHERE type = 'cpu-temp'
AND date BETWEEN (SELECT date('now', '-7 day'))
AND (SELECT date('now', '-1 day'))
GROUP BY date
ORDER BY date DESC
and:
SELECT *
FROM temperatureStatistics
WHERE date BETWEEN (SELECT date('now', '-7 day'))
AND (SELECT date('now', '-1 day'))
ORDER BY date DESC
But the first one is about three times as fast as the second one. What
am I doing wrong here?
Because your second query has to build the complete view before it can
decide if a result is between the selected dates?
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
