SQL INDEX 不用于 WHERE ABS(x-y) < k 条件，但用于 y - k < x < y + k 条件

Question

我有一个涉及 couples of rows which have a less-than-2-hours time-difference 的查询(~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))

使用时没有帮助:

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

使用时没有帮助:

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

...但在使用时大大提高了性能(查询时间很高兴除以 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 + Sqlite sqlite3模块

在执行 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><?)')

Answer 1

我相信包含 AND 是根据:

The WHERE clause on a query is broken up into "terms" where each term is separated from the others by an AND operator. If the WHERE clause is composed of constraints separate by the OR operator then the entire clause is considered to be a single "term" to which the OR-clause optimization is applied.

The SQLite Query Optimizer Overview

可能值得运行 ANALYZE 看看这是否会改善问题。

根据评论:

I think the previously added paragraph can clarify why ABS(x-y) < k is not using index, and why x < y + k is using it, don't you think so? Would you want to include this paragraph? [All terms of the WHERE clause are analyzed to see if they can be satisfied using indices. To be usable by an index a term must be of one of the following forms: column = expression, column IS expression, column > expression ...

已添加以下内容。

To be usable by an index a term must be of one of the following forms:
column = expression
column IS expression
column > expression
column >= expression
column < expression
column <= expression
expression = column
expression > column
expression >= column
expression < column
expression <= column
column IN (expression-list)
column IN (subquery)
column IS NULL

我不确定它是否适用于 BETWEEN (例如 WHERE column BETWEEN expr1 AND expr2 )。