LIKE 语句可以优化为不进行全表扫描吗? [英] Can the LIKE statement be optimized to not do full table scans?

问题描述

我想通过树路径从表中获取子树.

I want to get a subtree from a table by tree path.

path 列存储如下字符串:

foo/
foo/bar/
foo/bar/baz/

如果我尝试选择以特定路径开头的所有记录:

If I try to select all records that start with a certain path:

EXPLAIN QUERY PLAN SELECT * FROM f WHERE path LIKE "foo/%"

它告诉我表被扫描，即使 path 列被索引:(

it tells me that the table is scanned, even though the path column is indexed :(

有什么办法可以让 LIKE 使用索引而不扫描表吗?

Is there any way I could make LIKE use the index and not scan the table?

我找到了一种使用闭包表来实现我想要的方法，但是它更难维护并且写入速度非常慢...

I found a way to achieve what I want with closure table, but it's harder to maintain and writes are extremely slow...

推荐答案

为了能够在 SQLite 中使用 LIKE 的索引，

To be able to use an index for LIKE in SQLite,

1. 表列必须具有 TEXT affinity，即具有 TEXT 类型或VARCHAR 或类似的东西；和

2. 索引必须声明为 COLLATE NOCASE(或者直接声明，或者因为列已经声明为 COLLATE NOCASE):

1. the table column must have TEXT affinity, i.e., have a type of TEXT or VARCHAR or something like that; and

2. the index must be declared as COLLATE NOCASE (either directly, or because the column has been declared as COLLATE NOCASE):

> CREATE TABLE f(path TEXT);
> CREATE INDEX fi ON f(path COLLATE NOCASE);
> EXPLAIN QUERY PLAN SELECT * FROM f WHERE path LIKE 'foo/%';
0|0|0|SEARCH TABLE f USING COVERING INDEX fi (path>? AND path<?)

可以使用 case_sensitive_like PRAGMA 删除第二个限制，但这会改变LIKE 的行为.或者，可以使用区分大小写的比较，将 LIKE 'foo/%' 替换为 GLOB 'foo/*'.

The second restriction could be removed with the case_sensitive_like PRAGMA, but this would change the behaviour of LIKE. Alternatively, one could use a case-sensitive comparison, by replacing LIKE 'foo/%' with GLOB 'foo/*'.

这篇关于LIKE 语句可以优化为不进行全表扫描吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！