On Tue, Sep 9, 2008 at 10:18 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> "Stephen Oberholtzer" > <[EMAIL PROTECTED]> wrote in > message > news:[EMAIL PROTECTED]<[EMAIL PROTECTED]> > > Idea: Submit a patch that allows LIKE expressions that start with a > > fixed > > string (i.e. don't start with '%') to use the index to improve > > performance. (SQL Server 2000 does this.) > > http://www.sqlite.org/optoverview.html > 4.0 The LIKE optimization > > Igor Tandetnik > > "For the LIKE operator, if case_sensitive_like mode is enabled then the column must use the default BINARY collating sequence, or if case_sensitive_like mode is disabled then the column must use the built-in NOCASE collating sequence." There's a flaw in this design -- which explains why, when I actually tested it, the index wasn't used: CREATE TABLE foo (id integer primary key, name text, name_back text); INSERT INTO "foo" VALUES(1,'one','eno'); CREATE INDEX name_back_IX2 on foo(name_back collate nocase); pragma case_sensitive_like=off; explain query plan select name_back from foo where name_back like 'e%'; order from detail ---------- ---------- ---------- 0 0 TABLE foo The index "name_back_IX2" is collated NOCASE, which is the same collation that LIKE uses -- but the index doesn't get used! -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
