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?


I managed to shave off 30ms by reformatting the query in this form

  (SELECT breakType,date,blockname,
   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.

  • This is the answer I was looking for. Now, do you suggest making the entire table into the virtual table(but losing the ability for triggers and custom indexes) or just put the coordinates in the virtual table and use joins? May 13 '15 at 15:11
  • 1
    R-tree virtual tables contain only numbers; think of it as an index (that you have to join explicitly).
    – CL.
    May 13 '15 at 17:07
  • Allrighty then. The fun starts heh. May 13 '15 at 17:08

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.

  • No performance gain, and now it skips the dimension to and only filters on the Y column "0","0","0","SEARCH TABLE BlocksBrokenByExplosion USING INDEX BlocksBrokenByExplosion_Indexes2 (y>? AND y<?)" The y column is the most efficient to filter on first becuase usually there are only 70 different types in there, exclusing all other possibilities. Also the query is fastest when putting Y first instead of the others. With an index with only on the x and z I'm still having the same speed. May 12 '15 at 19:23
  • @MichaelDibbets I am confused. You say "no performance gain" and you also say "the query is fastest when putting Y first" as I suggested. But it could have been another column of course if the statistics were different. Yes it depends on your data, there are always variables.
    – RLF
    May 12 '15 at 19:33
  • What I mean is, Y is already used in the original query with the added benefit of the use of dimension. For the rest the performance is the same between 190-210 avg 200 ms. What i meant is, in the where part of the query I put y first because the query is fastest that way. if I put x or z first int he where part the time to execute goes to 350-370ms. if I put both x and z first the query time becomes 450-470ms. May 12 '15 at 19:35
  • Indexes are not affected by the order in the WHERE clause, but by position in the index and the cardinalty of the columns.
    – RLF
    May 12 '15 at 19:39
  • indexes possibly not, but execution speed is affected by order in where clause. May 12 '15 at 19:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.