Sqlite query slow whilst it should be fast

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.

The question

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?

EDIT 1

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

And index

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.