| Ticket Hash: | e090183531fc274747ee47477d793857f4f76b71 | ||
| Title: | like should use index when pattern has no wildcard | ||
| Status: | Fixed | Type: | Feature_Request |
| Severity: | Severe | Priority: | Immediate |
| Subsystem: | Unknown | Resolution: | Fixed |
| Last Modified: |
2010-07-22 12:34:28 14.77 years ago |
Created: |
2010-07-21 22:01:24 14.77 years ago |
| Version Found In: | 3.6.23.2 | ||
| Description: | ||||
|
As discussed by Dan Kennedy and Ben Trumbull in a recent mail thread:
It's arguably a defect. In this case the pattern used with LIKE must contain at least one wildcard character. So "value LIKE 'abc'" will not use the index, but "value LIKE 'abc%'" should. On Jul 21, 2010, at 1:59 PM, Ben Trumbull wrote: Hi Richard, Dan, We're having trouble getting section 4.0 like optimization in <http://www.sqlite.org/optoverview.html> to work as described. If I have: CREATE TABLE ABMultiValue (UID INTEGER PRIMARY KEY, record_id INTEGER, property INTEGER, identifier INTEGER, label INTEGER, value TEXT); CREATE INDEX testLike on ABMultiValue(value COLLATE NOCASE); and then compare = with LIKE (both collate nocase) I just can't get the LIKE expression to use the index: | ||||