Igor Tandetnik <igor at tandetnik.org> writes: > On 3/26/2016 6:12 PM, Denis Burke wrote: >> CREATE INDEX [IndxT1C3] >> ON [T1]( >> [C3] COLLATE [NOCASE]); >> ---------- >> after doing this: >> explain query plan >> select C1 from T1 >> where C3='2016-01-02' > > You are requesting a case sensitive comparison; a case insensitive > index cannot be used to satisfy it. > > If you expect SQLite to inspect the string literal character by > character and prove that case sensitivity won't make a difference, > then I'm afraid you expect too much.
It was already discussed a week ago. No, SQLite need not inspect string - any (even non-BINARY) index (theoretically) can be used to speed-up `COLLATE BINARY =` comparison; Queries SELECT * FROM T1 WHERE C3 COLLATE BINARY = ?1 and SELECT * FROM T1 WHERE (C3 COLLATE NOCASE = ?1) AND (C3 COLLATE BINARY = ?1) returns exactly same result (with *any* ?1), but (given that COLLATE NOCASE index exists and "good", but COLLATE BINARY index does not exists) second query can be much faster; on other hand, if NOCASE index is "bad" (i.e. there are very many records that [by COLLATE NOCASE rules] are equal to ?1), or if table is small, such replacement can become pessimisation, of course (and, reversely, if query optimized decided it won't use index for whatever reason, it can always replace `(C3 COLLATE NOCASE = ?1) AND (C3 COLLATE BINARY = ?1)` with `C3 COLLATE BINARY = ?1`). As SQLite query planner knows which indexes exists and good [by ANALYZE], it could've automatically replaced first query by second (but currently it is not). Obviously, this is NOT a bug, but just missed (non-obvious) optimization opportunity (and, well, "we want more optimizations" at certain point conflicts with "we want to keep sqlite lite" goal). And this optimization is limited to equality (=) only, it won't work with ORDER BY, <, > or BETWEEN. (Theoretically, it can be also used for to slightly speed-up GROUP BY or DISTINCT, but that's more complex [and, again, "complex optimization" and "lite" does not play together very well]).
