Hi, I hope this is a simple question that someone can answer without a lot of trouble (maybe I am simply misunderstanding something).
According to https://www.sqlite.org/optoverview.html#the_like_optimization , a LIKE query can use an ESCAPE character and still be optimized as long as the character is single-byte. According to https://www.sqlite.org/changes.html#version_3_21_0 this was added in version 3.21.0. I am a C++ programmer currently using version 3.24.0. I set PRAGMA case_sensitive_like=1 so my LIKE queries are not case-sensitive. My table has a column named 'path' that is set as UNIQUE, so the column is auto-indexed. When I run this: EXPLAIN QUERY PLAN SELECT * FROM MyTable WHERE path LIKE 'a%' I get this as a return value: SEARCH TABLE MyTable USING INDEX sqlite_autoindex_MyTable (path>? AND path<?) Looks good, the LIKE query can use the index. Yay. However, when I run this: EXPLAIN QUERY PLAN SELECT * FROM MyTable WHERE path LIKE 'a%' ESCAPE '_' I get this as a return value: SCAN TABLE MyTable I interpret that to mean that the LIKE query with an ESCAPE character is not using the index and is doing a full table scan. I tried this using SQLite 3.27.1 with the same result. Am I doing something wrong here, or perhaps misunderstanding the documentation? My expectation is that both LIKE queries will use the index, but the EXPLAIN QUERY PLAN results tell me something different. Thanks! - Julian _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
