Sqlite 查詢很慢，但應該很快

我有以下查詢：

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'

使用查詢計劃：

"0","0","0","SEARCH TABLE BlocksBrokenByExplosion 
USING INDEX BlocksBrokenByExplosion_Indexes (dimension=? AND y>? AND y<?)"

我使用的索引是：

 CREATE INDEX 'BlocksBrokenByExplosion_Indexes' ON  
      'BlocksBrokenByExplosion' ('dimension' ASC,'y' ASC,'x' ASC, 'z' ASC)

分析顯示以下資訊：

 "BlocksBrokenByExplosion","BlocksBrokenByExplosion_Indexes","78193 78193 2114 25 2"

我面臨的問題

這個查詢需要 200 毫秒來執行並返回 4409 行，在我看來這是非常糟糕的性能，我不禁覺得這可以優化。

該表僅包含 78.000 行，並且根據查詢計劃使用索引。

問題

如何優化此查詢，使其以更可接受的速度執行，或者這是我將使用 sqlite 獲得的最佳結果？

有誰知道為什麼沒有根據查詢計劃使用 x 和 z 索引？

編輯 1

通過以這種形式重新格式化查詢，我設法減少了 30 毫秒

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)

所以現在它在 170 毫秒內執行，但這仍然比我想要的慢。我的目標至少是 50 毫秒……所以我仍然願意接受如何加快速度的提示和技巧。

無法使用普通（B 樹）索引優化多個範圍查找。

您必須為您的座標創建一個R-tree 索引。

您的多部分索引覆蓋 4 列，但唯一支持查找的列是第一列。尾隨列 (y, x, z) 可用於查找行，但這可能不是最佳的。

如果最適合搜尋的列是y第一列。索引都是關於指導如何使用索引的統計數據。所以，也許這會更有效：

 CREATE INDEX 'BlocksBrokenByExplosion_Indexes' ON  
      'BlocksBrokenByExplosion' ('y' ASC,'x' ASC, 'z' ASC', dimension' ASC)

當然，正確答案取決於您的數據、每列的基數以及哪些索引列給出了最佳結果。

引用自：https://dba.stackexchange.com/questions/101323