Escape wildcards (%, _) in SQLite LIKE without sacrificing index use?

Question

I have a couple of issues with SQLite query. Actually I start thinking that SQLite is not designed for tables with more then 10 rows, really, SQLite is a nightmare.

The following query

SELECT * FROM [Table] WHERE [Name] LIKE 'Text%'

It works fine. EXPLAIN shows that the index is used and result is returned after about 70ms.

Now I need to run this query from .NET SQLite driver, so I'm changing query

SELECT * FROM [Table] WHERE [Name] LIKE @Pattern || '%'

Index is not used. When I run the following query in any SQLite tool the index is not used as well

SELECT * FROM [Table] WHERE [Name] LIKE 'Text' || '%'

So I guess SQLite doesn't have any kind of preprocessing logic implemented.

OK. Let's try to solve it, I'm still binding variables and doing the following

SELECT * FROM [Table] WHERE [Name] LIKE @Pattern

But now I append % wildcard symbol to the end of my pattern string, like this

command.Parameters.Add(new SQLiteParameter("@Pattern", pattern + '%'));

It works very slow. I can't say why, because when I run this query from SQLite tool it works fine, however when I bind this variable from .NET code it works slow.

OK. I'm still trying to solve this. I'm getting rid of the pattern parameter binding and building this condition dynamically.

pattern = pattern.Replace("'", "''");
pattern = pattern.Replace("%", "\\%");
where = string.Format("LIKE '{0}%' ESCAPE '\\'", pattern);

Index is not used again. It's not used because of ESCAPE. I see that when I run

EXPLAIN QUERY PLAN SELECT * FROM [Table] WHERE [Name] LIKE 'Text%' ESCAPE '\'

As soon as I remove ESCAPE it starts using index again and the query finishes in 60-70ms.

UPDATE

Here are the results.

EXPLAIN QUERY PLAN
SELECT * FROM [RegistryValues]
WHERE
     [ValueName] LIKE 'windir%' ESCAPE '\' 

SCAN TABLE RegistryValues (~3441573 rows)

and the one without ESCAPE

EXPLAIN QUERY PLAN
SELECT * FROM [RegistryValues]
WHERE
     [ValueName] LIKE 'windir%'

SEARCH TABLE RegistryValues USING INDEX IdxRegistryValuesValueNameKeyIdKeyHiveFileId (ValueName>? AND ValueName<?) (~31250 rows)

UPDATE

Just found this

http://www.sqlite.org/optoverview.html

4.0 The LIKE optimization

The ESCAPE clause cannot appear on the LIKE operator

So what should I do then?

Do I understand it right? I can't search string containing wildcards using LIKE operator in SQLite. By saying wildcards I mean _ % ^ !

It's impossible simply because I can't escape them. Actually I can, but I can't use indexes in this case, so the query will not be efficient.

Am I right?

Answer 1

An index can only be used with a LIKE clause when the % is at the end so that SQLite can rewrite it to two simple comparisons (as shown in the EXPLAIN output).

Therefore, to get the same effect, write the comparisons yourself. This requires that you construct some string that is guaranteed to compare 'larger' than any of the matched values (beware of non-ASCII characters). Instead of:

... WHERE Name LIKE 'Text%'

use:

... WHERE Name BETWEEN 'Text' AND 'Textzzzzzzzzzzzzz'

or, as parameter:

... WHERE Name BETWEEN @Pattern AND @Pattern || 'zzzzzzzzzz'

(This construct never needs escapes. :)

Answer 2

Since 3.21.0 (2017-10-24), LIKE optimization has been changed so that it works with an ESCAPE clause

Answer 3

Make sure you wrap your queries in a transaction. I had been doing some queries via a library without it and adding it increased the speed 10 fold.

Probably the best resource on performance issues: Improve INSERT-per-second performance of SQLite?