Sqlite stops using index if query contains concatenation syntax "||"-CodePudding

This is a query plan for a regular LIKE query. It uses an index as it should:

sqlite> PRAGMA case_sensitive_like=on;
sqlite> explain query plan select * from users where login like 'username%' limit 10;
QUERY PLAN
`--SEARCH users USING INDEX login_index (login>? AND login<?)

This is a query plan for the same query, but that uses concatenation || syntax:

sqlite> PRAGMA case_sensitive_like=on;
sqlite> explain query plan select * from users where login like 'username' || '%' limit 10;
QUERY PLAN
`--SCAN users

The same query doesn't use an index and switches to scanning whole table.

The reason I want to use concatenation || syntax is because otherwise username won't be recognized as a parameter, i.e. select * from users where login like '?1%' limit 10 doesn't work - sqlite considers that query has 0 parameters.

CodePudding user response：

That's normal, documented behavior. The LIKE optimization has a lot of rules and caveats about when it can be used; among them:

The right-hand side of the LIKE or GLOB must be either a string literal or a parameter bound to a string literal that does not begin with a wildcard character.

You're using an expression.

What you can try is appending the % to the end of the string you're binding to the parameter in the code that prepares and executes the query, not in the SQL statement: login LIKE ?, with username% as that parameter.