我有一个涉及几行时差小于2小时（〜0.08333天）的查询：

SELECT mt1.*, mt2.* FROM mytable mt1, mytable mt2 
                    WHERE ABS(JULIANDAY(mt1.date) - JULIANDAY(mt2.date)) < 0.08333

该查询相当慢，即〜1秒（该表具有〜10k行）。

一个主意是使用INDEX。显然CREATE INDEX id1 ON mytable(date)没有任何改善，这是正常的。

然后我注意到了神奇的查询 CREATE INDEX id2 ON mytable(JULIANDAY(date))

1. 使用时没有帮助：

   ... WHERE ABS(JULIANDAY(mt1.date) - JULIANDAY(mt2.date)) < 0.08333
   

2. 使用时没有帮助：

   ... WHERE JULIANDAY(mt2.date) - 0.08333 < JULIANDAY(mt1.date) < JULIANDAY(mt2.date) + 0.08333
   

3. …但是在使用时大大提高了性能（查询时间愉快地除以50！）：

   ... WHERE JULIANDAY(mt1.date) < JULIANDAY(mt2.date) + 0.08333
     AND JULIANDAY(mt1.date) > JULIANDAY(mt2.date) - 0.08333
   

当然1.，2和3.在数学上是等效的，

|x-y| < 0.08333 <=> y - 0.08333 < x < y + 0.08333
                <=> x < y + 0.08333 AND x > y - 0.08333

问题：为什么解决方案1.和2.不使用INDEX，而解决方案3.在使用它呢？

笔记：

• 我正在使用Python + Sqlitesqlite3模块

• 在执行EXPLAIN QUERY PLAN SELECT ...以下操作时，确认事实解决方案1.和2.未使用索引：

  (0, 0, 0, u'SCAN TABLE mytable AS mt1')
  

  (0, 1, 1, u’SCAN TABLE mytable AS mt2’)

执行时显示事实解决方案3.正在使用索引EXPLAIN QUERY PLAN SELECT ...：

    (0, 0, 1, u'SCAN TABLE mytable AS mt2')
(0, 1, 0, u'SEARCH TABLE mytable AS mt1 USING INDEX id2 (<expr>>? AND <expr><?)')