I have the following query:
SELECT breakType,date,blockname,modname,metadata,nbtdata,x,y,z FROM BlocksBrokenByExplosion WHERE dimension=0 AND y>60 AND y<70 AND x>150 AND x<250 AND z>150 AND z<250 AND date > '2015-05-08 00:10:00'
With a query plan:
"0","0","0","SEARCH TABLE BlocksBrokenByExplosion USING INDEX BlocksBrokenByExplosion_Indexes (dimension=? AND y>? AND y<?)"
The index I am using is:
CREATE INDEX 'BlocksBrokenByExplosion_Indexes' ON 'BlocksBrokenByExplosion' ('dimension' ASC,'y' ASC,'x' ASC, 'z' ASC)
Analyze shows the following information:
"BlocksBrokenByExplosion","BlocksBrokenByExplosion_Indexes","78193 78193 2114 25 2"
The problem i'm facing
This query takes 200ms to execute and return 4409 rows which is very poor performance in my opinion and I can't help but feeling this can be optimised.
The table only contains 78.000 rows and according to the query plan the index is used.
How do I optimise this query so it will execute in more acceptable speed, or is this the best I am going to get with sqlite?
And does anyone know why the x and z indexes are not being used according to the query plan?
I managed to shave off 30ms by reformatting the query in this form
SELECT * FROM (SELECT breakType,date,blockname, modname, metadata, nbtdata, x, y, z FROM BlocksBrokenByExplosion indexed BY BlocksBrokenByExplosion_IndexesX WHERE dimension=0 AND x>150 AND x<250 AND dimension = 0 AND date > '2015-05-08 00:10:00') WHERE z>150 AND z<250 AND y>60 AND y<70
CREATE INDEX 'BlocksBrokenByExplosion_IndexesX' ON 'BlocksBrokenByExplosion' ('x' ASC)
So now it runs in 170ms, but that's still slower than i'd want it. My target is 50ms at least... So i'm still open to tips and tricks how to make this faster.