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.