I have a very large sqlite table (70 million rows) on a low end machine that I am trying to index properly.
My columns are timestamp, machineid, ioid and value.
There are probably around 200 ioids (on each machine), a handful of machineids, and the timestamp is one sample per second for each ioid.
My requests are as such:
select * from AnalogInput where SampleInfo_source_timestamp>=0 and SampleInfo_source_timestamp<=1587481620000000000 and ioId in (1,9,10,11,5,6,7,8,12,13,14,15,179,203,2,3,27,28,29,31,32,33,34,35,36,37,30,38,42,48,41,49,53,59,52,60,64,70,63,79,89,82,134,144,137,71,74,75,78,16,479,490,20,26,19,90,94,100,93,101,105,111,104,133,112,115,116,122,181,182,233,177,171,504,505,173,172,174,175,211,212,217,213,214,215,216,176,234,235,236,237,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,219,220,221,222,223,237,238,239,240,241,225,226,227,230,231,232,228,229,506,507) and machineid=1111 group by ioId;
I am currently using an index of: CREATE INDEX "index1 $ AnalogInput" ON "AnalogInput" ("machineId", "ioId", "SampleInfo_source_timestamp")
Is it the most efficient command?