在WHERE ABS（x-y）上使用的SQL INDEX&lt; k条件，但用于y - k < x&lt; y + k条件

问题描述

我的查询涉及（〜 0.08333天）：

I have a query involving couples of rows which have a less-than-2-hours time-difference (~0.08333 days):

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

此查询相当慢，即~1秒（表格有~10k行）。

This query is rather slow, i.e. ~ 1 second (the table has ~ 10k rows).

一个想法是使用 INDEX 。显然 CREATE INDEX id1 ON mytable（日期）没有改善任何东西，这是正常的。

An idea was to use an INDEX. Obviously CREATE INDEX id1 ON mytable(date) didn't improve anything, that's normal.

然后我注意到神奇的查询 CREATE INDEX id2 ON mytable（JULIANDAY（date））

Then I noticed that the magical query CREATE INDEX id2 ON mytable(JULIANDAY(date))

1. 在使用时没有帮助：

1. didn't help when using:

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

在使用时没有帮助：

didn't help when using:

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

...但大幅改善了表现（查询快乐时间除以50！）使用时：

... but massively improved the performance (query time happily divided by 50 !) when using:

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

当然1.，2。和3.从数学上来说是等价的，

Of course 1., 2. and 3. are equivalent since mathematically,

|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 ... ：

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

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

推荐答案

我相信包含 AND 的推理依据：

I believe that the inclusion of AND is the reasoning as per :

运行，看看是否有所改善。

It may be worthwhile running ANALYZE to see if that improves matters.

根据评论：

已添加以下内容。

我不确定它是否适用于（例如 WHERE列BETWEEN expr1 AND expr2 ）。

I'm not sure if it would work with a BETWEEN (e.g. WHERE column BETWEEN expr1 AND expr2).

这篇关于在WHERE ABS（x-y）上使用的SQL INDEX&lt; k条件，但用于y - k < x&lt; y + k条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！