Details
-
Improvement
-
Resolution: Fixed
-
Minor
-
None
-
2.7.0
-
Security Level: Public
Description
Via this forum thread. It's tempting in a query to prefix-match docIDs using something like "...WHERE _id LIKE 'prefix:%'...". Intuitively this should be indexable because it's really a range, basically like "...WHERE _id BETWEEN 'prefix:' AND 'prefix:zzzzz". Unfortunately, it turns out in Couchbase Lite doing this prevents an optimized lookup using the primary key; instead it has to scan the entire table.
The SQLite query optimizer will optimize LIKE expressions this way, but only with restrictions that ensure it's really a range test. Particularly:
- For the LIKE operator, if case_sensitive_like mode is enabled then the column must indexed using BINARY collating sequence, or if case_sensitive_like mode is disabled then the column must indexed using built-in NOCASE collating sequence.
Since we enable `case_sensitive_like`, the above implies that we need to update the table schema to change `key TEXT PRIMARY KEY` to `key TEXT PRIMARY KEY COLLATE BINARY`, to enable this sort of query to be indexed. (Unfortunately I don't think it's possible to update an existing table this way, so only new databases would get this optimization.)