Python sqlite3 not using index with LIKE

Question

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%"

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) for the first query.
CREATE INDEX seqs_index ON sequences(seqs COLLATE NOCASE) for the second query.

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')]

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.

CL. · Accepted Answer · 2016-06-16 08:43:54Z

1

The documentation says:

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().

Old versions of the pysqlite module use sqlite3_prepare().

answered Jun 16, 2016 at 8:43

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

glormph Over a year ago

Thanks, using pysqlite from PyPI instead of the python std library sqlite3 (v.2.6.0) module fixes this. Unfortunately I'm on python3 and the pysqlite package is not available (yet). stackoverflow.com/a/23414147/1271862

Collectives™ on Stack Overflow

Python sqlite3 not using index with LIKE

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related