> On Oct 9, 2019, at 10:02 AM, Keith Medcalf <kmedc...@dessus.com> wrote: > > SUBSTR(name, 0, ?) is an expression, so unless you have an index on that > expression, then an index cannot be used to SEARCH for the rows.
That's accurate in general. However, there _is_ a very similar special-case optimization for the `like` function/operator when it's used to look for a prefix. (With all sorts of caveats having to do with which specific patterns represent prefix matches, edge conditions about data types, restrictions on collation and overloading of 'like'…) So I can see why it would be easy to think that SUBSTR has a similar optimization. It seems like there's a need for an optimizable string-prefix predicate, i.e. `str BEGINS WITH prefix` or `begins_with(str, prefix)`, to allow the developer to be more explicit about what they mean. Prefix matching is extremely common in some use cases. (One of our customers raised an issue about this a few days ago…) BETWEEN doesn't work well because it's inclusive, i.e. `BETWEEN 'foo' and 'fop'` doesn't work because it matches 'fop'. Coming up with the upper end of a string prefix match is super annoying — `BETWEEN 'foo' and 'foo\xff' only works until some wise guy adds the key `foo\xff` to the table, and is invalid UTF-8 anyway. —Jens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
