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.
Multiple ranges lookups cannot be optimized with normal (B-tree) indexes.
You have to create an R-tree index for your coordinates.
Your multipart index is covering 4 columns, but the only column that supports a seek is the first column. The trailing columns (y, x, z) can be used to find the rows, but this may not be optimal.
If the column best for a search is
y then make that the first column. Indexes are all about statistics which directs how the index can be used. So, perhaps this would be more effective:
CREATE INDEX 'BlocksBrokenByExplosion_Indexes' ON 'BlocksBrokenByExplosion' ('y' ASC,'x' ASC, 'z' ASC', dimension' ASC)
Of course, the correct answer depends on your data, the cardinality of each column, and which index columns give the best result.