"Kalyani Phadke" <[EMAIL PROTECTED]> wrote: > I am using Sqlite 3 as my database. One of my table contains 1280010 > rows. Db file size is 562,478KB. I am running DB on Windows XP pro-P4 > CPU 3.20GHz 3.19Hz ,2.00GB of RAM ) > > CREATE TABLE TableA > ( > ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > column1 VARCHAR (50) NOT NULL, > column2 VARCHAR (50) NOT NULL, > column3 TIMESTAMP NOT NULL DEFAULT (CURRENT_TIMESTAMP), > column4 VARCHAR (128) NULL, > column5 VARCHAR (255) NULL, > column6 VARCHAR ( 128 ) NULL, > column7 TEXT NULL, > column8 TEXT NULL > ) > I have select query which looks like > select ID from TableA where column2 like '%test%' or column4like > '%test%' or column5 like '%test%' or column6 like '%test%' or column7 > like '%test%' or column8 like '%test%' order by column3 desc; >
LIKE operators cannot use indices unless the index is case insensitive. Use GLOB for case sensitive fields. LIKE and GLOB operators cannot use indices if the pattern begins with a wildcard. Nothing in SQLite will use an index if you are connecting terms using OR. It looks like what you really want to use here is a full-text index. Please read about the FTS3 support in SQLite. That seems to be what you are trying to accomplish. -- D. Richard Hipp <[EMAIL PROTECTED]> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
