Thanks for your answer. I wrote a simple example to show the difference between last and previous version of sqlite. The problem is that I have some prepared statement which can be used either with wildchars or not, depending on user input; more complex (and less readable) code will needed if I have to check wildchar presence and choose the prepared statement. I think that it would be better if this will be resolved inside sqlite engine (and I suppose this is a common expected behaviour for a db).
>----Messaggio originale---- >Da: donald.gri...@allscripts.com >Data: 21/10/2009 20.15 >A: <ge...@iol.it>, "General Discussion of SQLite Database"<sqlite-us...@sqlite. org> >Ogg: RE: [sqlite] Like do not use index as previous version > > > >----- Original Message----- >From: sqlite-users-boun...@sqlite.org >[mailto:sqlite- users-boun...@sqlite.org] On Behalf Of ge...@iol.it >Sent: Wednesday, October 21, 2009 2:03 PM >To: sqlite-users@sqlite.org >Subject: [sqlite] Like do not use index as previous version > >Hi all, >it seems that in last versions on sqlite3 LIKE clause stopped to use >indexes; I created a new empty database with SQLIte 3.6.13 and I run >these statements : > >CREATE TABLE TEST >(TEXT_1 text PRIMARY KEY, TEXT_2 text, TEXT_3 text COLLATE NOCASE); >CREATE INDEX TEST_IDX_2 ON TEST (TEXT_2); CREATE INDEX >TEST_IDX_3 ON TEST (TEXT_3); > >Running explain query plan on "select * from test where text_3 like >'x';", I have the following result: > >0|0|TABLE test WITH >INDEX TEST_IDX_3 > >And it's what I expected. > >If I execute the some statements >in SQLite 3.6.16 and 3.6.19, I have this result: > >0|0|TABLE test > >So It's not >using the index as in 3.6.13 version. >The some if I try to use the operator >GLOB with field text_2: in SQLite 3.6.13 it uses the TEST_IDX_2 index >but it's >not used in SQLite 3.6.19. > >Any suggestions? > >========================== > >Regarding suggestions: > Since "like" and "glob" are intended for use with wildcards and >you're not using wildcards, why not use > "where text_3 == 'x';" >instead? > > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users