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.

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