I'm not 100% sure but
Try splitting your query out into 6 different selects, I think the OR clause
is the problem...
select ID from TableA where column2 like '%test%'
union
select ID from TableA where column4 like '%test%'
....
HTH
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;
Without Index
1000rows in 8.103745seconds
With Index on column3
1000 row(s) affected in 8.21403 second(s).
With Index on column3 ,column4 , column5 , column6 , column7 ,
column8
1000 row(s) affected in 8.007997 second(s).
So after adding index there is no improvement on the query execution. I
found that The GLOB and LIKE operators are expensive in SQLite because
they can't make use of an index. One reason is that these are
implemented by user functions, which can be overridden, so the parser
has no way of knowing how they might behave in that case. This forces a
full scan of the table for the column being matched against, even if
that column has an index.
Is there any way I can improve Query performance?
Appreciate your help.
Thanks
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
