SQLite – How to Optimize Slow Query Performance

optimizationsqlite

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.

Best Answer

Multiple ranges lookups cannot be optimized with normal (B-tree) indexes.

You have to create an R-tree index for your coordinates.

Related Question