[sqlite] Index Selection
Igor Tandetnik 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]).
[sqlite] Index Selection
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. -- Igor Tandetnik
[sqlite] Index Selection
I apologize if I am missing something elementary here. I cannot understand
why this index is not helpful.
CREATE TABLE [T1](
[C1] TEXT,
[C2] TEXT,
[C3] DATE);
CREATE INDEX [IndxT1C3]
ON [T1](
[C3] COLLATE [NOCASE]);
insert into t1 values
('aa','bb','2016-01-01'),
('ab','bb','2016-01-02'),
('ac','bc','2016-01-03');
analyze;
--
after doing this:
explain query plan
select C1 from T1
where C3='2016-01-02'
> 0, 0, 0,0 SCAN TABLE T1
and
explain query plan
select C1 from T1 indexed by IndxT1C3
where C3='2016-01-02'
> no query solution
Why can this index not be used?
Thank you,
Denis Burke
[sqlite] Index Selection
No, it is working perfectly.
The index cannot be used because the equal (=) operator is collate BINARY.
If you want the column C3 to be not case sensitive you declare the table thusly:
.eqp on
create table t1
(
c1 text,
c2 text,
c3 text collate nocase
);
create index IndxT1C3 on T1(C3);
insert into t1 values
('aa','bb','2016-01-01'),
('ab','bb','2016-01-02'),
('ac','bc','2016-01-03');
analyze;
select C1 from T1 where C3='2016-01-03';
--EQP-- 0,0,0,SEARCH TABLE T1 USING INDEX IndxT1C3 (c3=?)
ac
Alternatively, you can specify that you want the "=" operator to use NOCASE
collation rather than BINARY collation.
.eqp on
create table t1
(
c1 text,
c2 text,
c3 date
);
create index IndxT1C3 on T1(C3 collate nocase);
insert into t1 values
('aa','bb','2016-01-01'),
('ab','bb','2016-01-02'),
('ac','bc','2016-01-03');
analyze;
select C1 from T1 where C3='2016-01-03' collate nocase;
--EQP-- 0,0,0,SEARCH TABLE T1 USING INDEX IndxT1C3 (c3=?)
ac
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Denis Burke
> Sent: Saturday, 26 March, 2016 16:12
> To: SQLite mailing list
> Subject: [sqlite] Index Selection
>
> I apologize if I am missing something elementary here. I cannot
> understand
> why this index is not helpful.
>
> CREATE TABLE [T1](
> [C1] TEXT,
> [C2] TEXT,
> [C3] DATE);
>
> CREATE INDEX [IndxT1C3]
> ON [T1](
> [C3] COLLATE [NOCASE]);
>
> insert into t1 values
> ('aa','bb','2016-01-01'),
> ('ab','bb','2016-01-02'),
> ('ac','bc','2016-01-03');
>
> analyze;
>
> --
> after doing this:
> explain query plan
> select C1 from T1
> where C3='2016-01-02'
>
> > 0, 0, 0,0 SCAN TABLE T1
>
> and
>
> explain query plan
> select C1 from T1 indexed by IndxT1C3
> where C3='2016-01-02'
>
> > no query solution
>
>
> Why can this index not be used?
>
> Thank you,
> Denis Burke
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
