-----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