Python sqlite3不与LIKE一起使用索引 [英] Python sqlite3 not using index with LIKE

问题描述

我有一个带有单列的表，我用两种方式查询它:

I have a table with a single column, which I query in two ways:

• SELECT * FROM sequences WHERE seqs="blablabla"
• SELECT * FROM sequences WHERE seqs LIKE "blablabla%"

• SELECT * FROM sequences WHERE seqs="blablabla"
• SELECT * FROM sequences WHERE seqs LIKE "blablabla%"

要使这些查询使用索引，我似乎需要两个索引(每种查询类型一个)，例如:

For these queries to use an index I seem to need two indices (one for each query type), as such:

• CREATE INDEX test_nocol ON sequences(seqs)用于第一个查询.
• CREATE INDEX seqs_index ON sequences(seqs COLLATE NOCASE)用于第二个查询.

• CREATE INDEX test_nocol ON sequences(seqs) for the first query.
• CREATE INDEX seqs_index ON sequences(seqs COLLATE NOCASE) for the second query.

这很好，但是随后我添加了python3的sqlite3模块，并开始在那里查询，该模块适用于原始字符串，但是当我使用参数绑定时，突然不再使用COLLATE索引:

That's all nice, but then I add python3's sqlite3 module, and start querying there instead, which works with raw strings, but when I use parameter bindings the COLLATE index is suddenly no longer used:

>>> sql = 'explain query plan\n select seqs from sequences where seqs="blabla"'
>>> c3.execute(sql).fetchall()
[(0, 0, 0, 'SEARCH TABLE sequences USING COVERING INDEX test_nocol (seqs=?)')]
>>> sql = 'explain query plan\n select seqs from sequences where seqs=?'
>>> c3.execute(sql, ('hahahah',)).fetchall()
[(0, 0, 0, 'SEARCH TABLE sequences USING COVERING INDEX test_nocol (seqs=?)')]
>>> sql = 'explain query plan\n select seqs from sequences where seqs like "hahahah%"'
>>> c3.execute(sql).fetchall()
[(0, 0, 0, 'SEARCH TABLE sequences USING COVERING INDEX seqs_index (seqs>? AND seqs<?)')]
>>> sql = 'explain query plan\n select seqs from sequences where seqs like ?'
>>> c3.execute(sql, ('hahahah',)).fetchall()
[(0, 0, 0, 'SCAN TABLE sequences')]

我在这里做错了什么?由于这是序列化后端，而不是webapp数据库，因此我认为使用原始字符串时的威胁不太严重，但是我宁愿使用正确的SQL格式.

What am I doing wrong here? Since this is a serialization backend and not a webapp DB, I guess the threat when using raw strings is less severe, but I'd much rather use the proper SQL formatting.

推荐答案

文档说:

如果右侧是绑定到字符串的参数，那么只有在使用准备好的语句时，才尝试进行此优化. ="http://www.sqlite.org/c3ref/prepare.html" rel ="nofollow"> sqlite3_prepare_v2()或参数并且语句为使用 sqlite3_prepare()或

If the right-hand side is a parameter that is bound to a string, then this optimization is only attempted if the prepared statement containing the expression was compiled with sqlite3_prepare_v2() or sqlite3_prepare16_v2(). The LIKE optimization is not attempted if the right-hand side is a parameter and the statement was prepared using sqlite3_prepare() or sqlite3_prepare16().

pysqlite模块的旧版本使用sqlite3_prepare().

Old versions of the pysqlite module use sqlite3_prepare().

这篇关于Python sqlite3不与LIKE一起使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！