sqlite 在查询中使用IS NULL时索引不起作用

piv4azn7  于 2022-11-15  发布在  SQLite
关注(0)|答案(1)|浏览(198)

我有以下索引:

CREATE INDEX IF NOT EXISTS dateIdx ON table1 (DateFirstAdded COLLATE NOCASE)

查询的以下ORDER BY部分成功使用了该索引:

ORDER BY DateFirstAdded COLLATE NOCASE ASC LIMIT 100

但以下情况并非如此:

ORDER BY DateFirstAdded is null COLLATE NOCASE, DateFirstAdded COLLATE NOCASE ASC LIMIT 100

它是我需要建立的新索引,还是查询调整?

u0sqgete

u0sqgete1#

在从表中读取记录之后,不能使用索引来加快排序速度;使用索引的唯一方法是在查询从表中读取数据时遍历索引项。
这意味着索引中条目的顺序必须与ORDER BY子句中指定的顺序完全相同。
在本例中,没有将DateFirstAdded IS NULL作为第一列的索引(并且SQLite不支持表达式索引);您的索引在末尾有空记录。(请注意,IS NULL返回0或1,这不需要排序规则。)
要最后返回空记录并仍使用索引进行排序,您必须将查询拆分成分别返回非空记录和空记录的复合查询;要能够使用ORDER BY,您必须使用子查询:

SELECT *
FROM (SELECT *
      FROM table1
      WHERE DateFirstAdded IS NOT NULL         -- uses index
      ORDER BY DateFirstAdded COLLATE NOCASE)  -- implied by index scan
UNION ALL
SELECT *
FROM table1
WHERE DateFirstAdded IS NULL                   -- uses index
LIMIT 100

相关问题