SQLite：应该LIKE'searchstr％'使用索引吗？ [英] SQLite: Should LIKE 'searchstr%' use an index?

问题描述

我有一个包含多个字段的数据库

I have a DB with several fields

word_id — INTEGER PRIMARY_KEY
word — TEXT
...

..和〜150k行。

..and ~150k rows.

由于这是一个字典，我使用LIKE搜索一个带有掩码'search_string％'的单词。
它用来工作很好，花了15毫秒来找到匹配的行。该表具有字段'word'的索引。
最近我修改了表（该表的一些字段超出范围），发生了一些事 - 它花了400毫秒执行查询，所以我明白，因为它没有使用索引现在。
使用=而不是like显示10ms结果的简单查询。
有人知道这里发生了什么吗？

Since this is a dictionary, I'm searching for a word with mask 'search_string%' using LIKE. It used to work just fine, taking 15ms to find matching rows. The table has an index for a field 'word'. Recently I've modified the table (some fields of that table which are out of the scope) and something happened — it's taking 400ms to execute query, so I understand that as it fails to use index now. Straightforward query with = instead of like shows 10ms result. Does someone have an idea what's happening here?

推荐答案

索引在这种情况下不能安全使用。一个天真的实现将转换这个：

An index cannot safely be used in this case. A naive implementation would transform this:

... WHERE字LIKE'search_string％'

into

... WHERE word> ='search_string'AND word& 'search_strinh'

通过递增搜索字符串的最后一个字符。大于和小于运算符可以使用索引，LIKE不能。

by incrementing the last character of the search string. The greater-than and less-than operators can use an index, where LIKE cannot.

不幸的是，这在一般情况下不起作用。 LIKE 运算符不区分大小写，这意味着'a'LIKE'A'是真的。

Unfortunately, that won't work in the general case. The LIKE operator is case-insensitive, which means that 'a' LIKE 'A' is true. The above transformation would break any search string with capitalized letters.

但在某些情况下，您知道大小写敏感性与特定的列，上面的转换是安全的。在这种情况下，您有两个选项。

In some cases, however, you know that case sensitivity is irrelevant for a particular column, and the above transformation is safe. In this case, you have two options.

1. 使用 NOCASE c $>更改 LIKE 运算符程序范围的行为，通过运行 PRAGMA case_sensitive_like = ON;

1. Use the NOCASE collating sequence on the index that covers this particular field.
2. Change the behavior of the LIKE operator program-wide by running PRAGMA case_sensitive_like = ON;

这两种行为都将使SQLite透明地为你;你只要一直使用 LIKE ，SQLite将重写基础查询以使用索引。

Either of these behaviors will enable SQLite to transparently do the above transformation for you; you just keep using LIKE as always, and SQLite will rewrite the underlying query to use the index.

请参阅 SQLite查询优化工具概述页面上的LIKE优化。

You can read more about "The LIKE Optimization" on the SQLite Query Optimizer Overview page.

这篇关于SQLite：应该LIKE'searchstr％'使用索引吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！