I've got this (simplified) table schema:
CREATE TABLE [Services] ([Id] INTEGER PRIMARY KEY, [AssetId] INTEGER NULL,
[Name] TEXT NOT NULL);
CREATE TABLE [Telemetry] ([Id] INTEGER PRIMARY KEY, [ServiceId] INTEGER
NULL, [Name] TEXT NOT NULL)
CREATE TABLE [Events] ([Id] INTEGER PRIMARY KEY, [TelemetryId] INTEGER NOT
NULL, [TimestampTicks] INTEGER NOT NULL, [Value] TEXT NOT NULL)
The Services and Telemetry tables have hundreds of rows, but the Events
table has a million. I can't seem to figure out the right combination of
indexes to get the following queries to all execute quickly. Some indexes
help some queries but hurt others. When I have indexes on TelemetryId and
TimestampTicks the former seems to be favored even when the TimestampTicks
index provides quicker results. When I use the '+' to disable the
TelemetryId index I get some fast and some slow results. Help?
SELECT MIN/MAX(e.TimestampTicks) FROM Events e INNER JOIN Telemetry t ON
t.ID = e.TelemetryID INNER JOIN Services s ON s.ID = t.ServiceID WHERE
s.AssetID = @AssetId;
SELECT e1.* FROM Events e1 INNER JOIN Telemetry t1 ON t1.Id = e1.TelemetryId
INNER JOIN Services s1 ON s1.Id = t1.ServiceId WHERE t1.Name =
@TelemetryName AND s1.Name = @ServiceName;
SELECT * FROM Events e INNER JOIN Telemetry t ON t.Id = e.TelemetryId INNER
JOIN Services s ON s.Id = t.ServiceId WHERE s.AssetId = @AssetId AND
e.TimestampTicks >= @StartTimeTicks ORDER BY e.TimestampTicks LIMIT 1000;
SELECT e.Id, e.TelemetryId, e.TimestampTicks, e.Value FROM (
SELECT e2.Id AS [Id], MAX(e2.TimestampTicks) as
[TimestampTicks]
FROM Events e2 INNER JOIN Telemetry t ON
t.Id = e2.TelemetryId INNER JOIN Services s ON s.Id = t.ServiceId
WHERE s.AssetId = @AssetId AND
e2.TimestampTicks <= @StartTimeTicks
GROUP BY e2.TelemetryId) AS grp
INNER JOIN Events e ON grp.Id = e.Id;
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
