Re: [sqlite] SQLite Like Query Optimization
I have downloaded the amalgamation . I used devC++ builder/compiler by
bloodshed software.
The steps I took to compile the project
1. Create SQLite3 Project
2. Add files to project (sqlite3.c,sqlite3.h,sqlite3ext.h)
3.Go to Project -project options
4.Under Compiler tab ,select optimization->further optimization->best
optimization.
5. Under parameters tab,Additional command line options
Compiler: -DBUILDING_DLL=1
-DSQLITE_ENABLE_FTS3=1
Linker: --no-export-all-symbols --add-stdcall-alias
--def sqlite3.def
6.Create Win32 dll
7. Compile
8. Creates Sqlite3.dll without any error.
Now I copied this file into D:\Databases\SQLitedb\SqliteFTS folder.
I also copied Sqlite3.exe to this folder.
>From the command prompt I tried the following things
C:\>D:\Databases\SQLitedb\SqliteFTS\Sqlite3.exe
Sqlite> .load libfts3.dll
Unable to open shared library libfts3.dll
Sqlite>select load_extension('libfts3.dll');
Sql error:unable to open shared library libfts3.dll
Sqlite> .load fts3
Unable to open shared library [fts3]
Sqlite>select load_extension('fts3');
Sql error:unable to open shared library [fts3]
So how can I use newly created Sqlite3.dll from command prompt to load
fts.
Thanks
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
Sent: Monday, February 18, 2008 2:22 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite Like Query Optimization
"Kalyani Phadke" <[EMAIL PROTECTED]> wrote:
> I have not recompiled sqlite 3 before. I am having trouble to find
> documentation.
>
> Could anyone pls tell me how can I compile SQLite3 source code on
> windows xp machine. Do I need to download FTS3 files ? Where can I
> find those files? How can I add this extension to my sqlite???
>
(1) Download the amalgamation.
(2) Compile with -DSQLITE_ENABLE_FTS3=1
--
D. Richard Hipp <[EMAIL PROTECTED]>
___
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
Re: [sqlite] SQLite Like Query Optimization
"Kalyani Phadke" <[EMAIL PROTECTED]> wrote: > I have not recompiled sqlite 3 before. I am having trouble to find > documentation. > > Could anyone pls tell me how can I compile SQLite3 source code on > windows xp machine. Do I need to download FTS3 files ? Where can I find > those files? How can I add this extension to my sqlite??? > (1) Download the amalgamation. (2) Compile with -DSQLITE_ENABLE_FTS3=1 -- D. Richard Hipp <[EMAIL PROTECTED]> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Like Query Optimization
On 2/18/08, Kalyani Phadke <[EMAIL PROTECTED]> wrote:
> I have not recompiled sqlite 3 before. I am having trouble to find
> documentation.
>
> Could anyone pls tell me how can I compile SQLite3 source code
http://www.sqlite.org/cvstrac/wiki?p=CompilingFts
> windows xp machine.
no idea about Windows.
> Do I need to download FTS3 files ? Where can I find
> those files? How can I add this extension to my sqlite???
The FTS source comes with the SQLite source (look in the ext folder).
See the link above for detailed steps that worked for me on a Mac.
>
> Thanks
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of P Kishor
> Sent: Monday, February 18, 2008 11:10 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLite Like Query Optimization
>
> On 2/18/08, Kalyani Phadke <[EMAIL PROTECTED]> wrote:
> > I am trying to use FTS3 with SQlite3 . Do I need to recompile SQlite3
>
> > to enable FTS3?
>
> yes.
>
> >
> > From the command prompt I tried the following things
> >
> > Sqlite> .load libfts3.dll
> > Unable to open shared library libfts3.dll
> > Sqlite>select load_extension('libfts3.dll');
> > Sql error:unable to open shared library libfts3.dll
> >
> > So my question is The FTS3 module is available in SQLite version 3.5.6
>
> > and later?
> >
> > Thanks,
> >
> >
> >
> >
> >
> > -Original Message-----
> > From: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED] On Behalf Of P Kishor
> > Sent: Monday, February 18, 2008 9:50 AM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] SQLite Like Query Optimization
> >
> > On 2/18/08, Kalyani Phadke <[EMAIL PROTECTED]> wrote:
> > >
> > > Suppose User typed 'test' in search text box, I would like to search
>
> > > the 'test' string in all the coulmns ... I do not want exact match..
> > > The columns could contain strings like 'tester' or 'tested' . I
> > > should be able to get these records as well..
> > >
> > > Hope I am clear explaining what I want..
> >
> > you definitely should look into implementing full-text search using
> > fts3. It will solve your problems as well as world peace.
> >
> >
> > >
> > > -Thanks
> > >
> > >
> > > -Original Message-
> > > From: [EMAIL PROTECTED]
> > > [mailto:[EMAIL PROTECTED] On Behalf Of BareFeet
> > > Sent: Friday, February 15, 2008 4:38 PM
> > > To: General Discussion of SQLite Database
> > > Subject: Re: [sqlite] SQLite Like Query Optimization
> > >
> > > Hi Kalyani,
> > >
> > > > 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;
> > >
> > > As already stated, the like operator can't use indexes if you use
> > > "or", or start with a wild card.
> > >
> > > Is each '%test%' in your example meant to be the same string, or
> > > different strings? If different, then what exactly is each column
> > > storing, and what are you trying to search for? Perhaps you could
> > > make
> >
> > > each column more "atomic" by splitting the contents into more
> > > columns,
> >
> > > which you could then search using "=" instead of "like" and so use
> > > indexes.
> > >
> > > Tom
> > > BareFeet
> > >
> > > --
> > > One stop Australian on-line shop for Macs and accessories
> > > http://www.tandb.com.au/forsale/?ml
> > >
> > > ___
> > > 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
> > >
> > ___
> > 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
> >
> ___
> 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
Re: [sqlite] SQLite Like Query Optimization
I have not recompiled sqlite 3 before. I am having trouble to find
documentation.
Could anyone pls tell me how can I compile SQLite3 source code on
windows xp machine. Do I need to download FTS3 files ? Where can I find
those files? How can I add this extension to my sqlite???
Thanks
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of P Kishor
Sent: Monday, February 18, 2008 11:10 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite Like Query Optimization
On 2/18/08, Kalyani Phadke <[EMAIL PROTECTED]> wrote:
> I am trying to use FTS3 with SQlite3 . Do I need to recompile SQlite3
> to enable FTS3?
yes.
>
> From the command prompt I tried the following things
>
> Sqlite> .load libfts3.dll
> Unable to open shared library libfts3.dll
> Sqlite>select load_extension('libfts3.dll');
> Sql error:unable to open shared library libfts3.dll
>
> So my question is The FTS3 module is available in SQLite version 3.5.6
> and later?
>
> Thanks,
>
>
>
>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of P Kishor
> Sent: Monday, February 18, 2008 9:50 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLite Like Query Optimization
>
> On 2/18/08, Kalyani Phadke <[EMAIL PROTECTED]> wrote:
> >
> > Suppose User typed 'test' in search text box, I would like to search
> > the 'test' string in all the coulmns ... I do not want exact match..
> > The columns could contain strings like 'tester' or 'tested' . I
> > should be able to get these records as well..
> >
> > Hope I am clear explaining what I want..
>
> you definitely should look into implementing full-text search using
> fts3. It will solve your problems as well as world peace.
>
>
> >
> > -Thanks
> >
> >
> > -Original Message-
> > From: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED] On Behalf Of BareFeet
> > Sent: Friday, February 15, 2008 4:38 PM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] SQLite Like Query Optimization
> >
> > Hi Kalyani,
> >
> > > 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;
> >
> > As already stated, the like operator can't use indexes if you use
> > "or", or start with a wild card.
> >
> > Is each '%test%' in your example meant to be the same string, or
> > different strings? If different, then what exactly is each column
> > storing, and what are you trying to search for? Perhaps you could
> > make
>
> > each column more "atomic" by splitting the contents into more
> > columns,
>
> > which you could then search using "=" instead of "like" and so use
> > indexes.
> >
> > Tom
> > BareFeet
> >
> > --
> > One stop Australian on-line shop for Macs and accessories
> > http://www.tandb.com.au/forsale/?ml
> >
> > ___
> > 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
> >
> ___
> 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
>
___
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
Re: [sqlite] SQLite Like Query Optimization
On 2/18/08, Kalyani Phadke <[EMAIL PROTECTED]> wrote:
> I am trying to use FTS3 with SQlite3 . Do I need to recompile SQlite3
> to enable FTS3?
yes.
>
> From the command prompt I tried the following things
>
> Sqlite> .load libfts3.dll
> Unable to open shared library libfts3.dll
> Sqlite>select load_extension('libfts3.dll');
> Sql error:unable to open shared library libfts3.dll
>
> So my question is The FTS3 module is available in SQLite version 3.5.6
> and later?
>
> Thanks,
>
>
>
>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of P Kishor
> Sent: Monday, February 18, 2008 9:50 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLite Like Query Optimization
>
> On 2/18/08, Kalyani Phadke <[EMAIL PROTECTED]> wrote:
> >
> > Suppose User typed 'test' in search text box, I would like to search
> > the 'test' string in all the coulmns ... I do not want exact match..
> > The columns could contain strings like 'tester' or 'tested' . I
> > should be able to get these records as well..
> >
> > Hope I am clear explaining what I want..
>
> you definitely should look into implementing full-text search using
> fts3. It will solve your problems as well as world peace.
>
>
> >
> > -Thanks
> >
> >
> > -Original Message-
> > From: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED] On Behalf Of BareFeet
> > Sent: Friday, February 15, 2008 4:38 PM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] SQLite Like Query Optimization
> >
> > Hi Kalyani,
> >
> > > 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;
> >
> > As already stated, the like operator can't use indexes if you use
> > "or", or start with a wild card.
> >
> > Is each '%test%' in your example meant to be the same string, or
> > different strings? If different, then what exactly is each column
> > storing, and what are you trying to search for? Perhaps you could make
>
> > each column more "atomic" by splitting the contents into more columns,
>
> > which you could then search using "=" instead of "like" and so use
> > indexes.
> >
> > Tom
> > BareFeet
> >
> > --
> > One stop Australian on-line shop for Macs and accessories
> > http://www.tandb.com.au/forsale/?ml
> >
> > ___
> > 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
> >
> ___
> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Like Query Optimization
I am trying to use FTS3 with SQlite3 . Do I need to recompile SQlite3
to enable FTS3?
>From the command prompt I tried the following things
Sqlite> .load libfts3.dll
Unable to open shared library libfts3.dll
Sqlite>select load_extension('libfts3.dll');
Sql error:unable to open shared library libfts3.dll
So my question is The FTS3 module is available in SQLite version 3.5.6
and later?
Thanks,
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of P Kishor
Sent: Monday, February 18, 2008 9:50 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite Like Query Optimization
On 2/18/08, Kalyani Phadke <[EMAIL PROTECTED]> wrote:
>
> Suppose User typed 'test' in search text box, I would like to search
> the 'test' string in all the coulmns ... I do not want exact match..
> The columns could contain strings like 'tester' or 'tested' . I
> should be able to get these records as well..
>
> Hope I am clear explaining what I want..
you definitely should look into implementing full-text search using
fts3. It will solve your problems as well as world peace.
>
> -Thanks
>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of BareFeet
> Sent: Friday, February 15, 2008 4:38 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLite Like Query Optimization
>
> Hi Kalyani,
>
> > 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;
>
> As already stated, the like operator can't use indexes if you use
> "or", or start with a wild card.
>
> Is each '%test%' in your example meant to be the same string, or
> different strings? If different, then what exactly is each column
> storing, and what are you trying to search for? Perhaps you could make
> each column more "atomic" by splitting the contents into more columns,
> which you could then search using "=" instead of "like" and so use
> indexes.
>
> Tom
> BareFeet
>
> --
> One stop Australian on-line shop for Macs and accessories
> http://www.tandb.com.au/forsale/?ml
>
> ___
> 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
>
___
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
Re: [sqlite] SQLite Like Query Optimization
On 2/18/08, Kalyani Phadke <[EMAIL PROTECTED]> wrote: > > Suppose User typed 'test' in search text box, I would like to search the > 'test' string in all the coulmns ... I do not want exact match.. The > columns could contain strings like 'tester' or 'tested' . I should be > able to get these records as well.. > > Hope I am clear explaining what I want.. you definitely should look into implementing full-text search using fts3. It will solve your problems as well as world peace. > > -Thanks > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of BareFeet > Sent: Friday, February 15, 2008 4:38 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] SQLite Like Query Optimization > > Hi Kalyani, > > > 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; > > As already stated, the like operator can't use indexes if you use "or", > or start with a wild card. > > Is each '%test%' in your example meant to be the same string, or > different strings? If different, then what exactly is each column > storing, and what are you trying to search for? Perhaps you could make > each column more "atomic" by splitting the contents into more columns, > which you could then search using "=" instead of "like" and so use > indexes. > > Tom > BareFeet > > -- > One stop Australian on-line shop for Macs and accessories > http://www.tandb.com.au/forsale/?ml > > ___ > 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Like Query Optimization
Suppose User typed 'test' in search text box, I would like to search the 'test' string in all the coulmns ... I do not want exact match.. The columns could contain strings like 'tester' or 'tested' . I should be able to get these records as well.. Hope I am clear explaining what I want.. -Thanks -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of BareFeet Sent: Friday, February 15, 2008 4:38 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite Like Query Optimization Hi Kalyani, > 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; As already stated, the like operator can't use indexes if you use "or", or start with a wild card. Is each '%test%' in your example meant to be the same string, or different strings? If different, then what exactly is each column storing, and what are you trying to search for? Perhaps you could make each column more "atomic" by splitting the contents into more columns, which you could then search using "=" instead of "like" and so use indexes. Tom BareFeet -- One stop Australian on-line shop for Macs and accessories http://www.tandb.com.au/forsale/?ml ___ 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
Re: [sqlite] SQLite Like Query Optimization
"Evans, Mark (Tandem)" <[EMAIL PROTECTED]> wrote:
> snip...
>
> >
> > LIKE operators cannot use indices unless the index is case
> > insensitive. Use GLOB for case sensitive fields.
> >
>
> Richard - i'm not sure i understand "unless the index is case insensitive."
> How does that relate to:
>
> sqlite> create table t (a varchar(10) primary key, b, c);
> sqlite> pragma CASE_SENSITIVE_LIKE=OFF;
> sqlite> explain query plan select * from t where a like 'a%';
> 0|0|TABLE t
> sqlite> pragma CASE_SENSITIVE_LIKE=ON;
> sqlite> explain query plan select * from t where a like 'a%';
> 0|0|TABLE t WITH INDEX sqlite_autoindex_t_1
> sqlite>
>
> Dumb question: Is CASE_SENSITIVE_LIKE a different concept
> from "case sensitive index"?
>
Yes it is. By default ('A' LIKE 'a') is true. But if you enable
case-sensitive like then ('A' LIKE 'a') is false.
The case sensitivity of your LIKE operator must match the
case sensitivity of your indices in order for the index
to be usable as an optimization.
--
D. Richard Hipp <[EMAIL PROTECTED]>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Like Query Optimization
...snip... > > LIKE operators cannot use indices unless the index is case > insensitive. Use GLOB for case sensitive fields. > Richard - i'm not sure i understand "unless the index is case insensitive." How does that relate to: sqlite> create table t (a varchar(10) primary key, b, c); sqlite> pragma CASE_SENSITIVE_LIKE=OFF; sqlite> explain query plan select * from t where a like 'a%'; 0|0|TABLE t sqlite> pragma CASE_SENSITIVE_LIKE=ON; sqlite> explain query plan select * from t where a like 'a%'; 0|0|TABLE t WITH INDEX sqlite_autoindex_t_1 sqlite> Dumb question: Is CASE_SENSITIVE_LIKE a different concept from "case sensitive index"? > 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]> > Thanks, Mark ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Like Query Optimization
Hi Kalyani, > 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; As already stated, the like operator can't use indexes if you use "or", or start with a wild card. Is each '%test%' in your example meant to be the same string, or different strings? If different, then what exactly is each column storing, and what are you trying to search for? Perhaps you could make each column more "atomic" by splitting the contents into more columns, which you could then search using "=" instead of "like" and so use indexes. Tom BareFeet -- One stop Australian on-line shop for Macs and accessories http://www.tandb.com.au/forsale/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Like Query Optimization
"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
Re: [sqlite] SQLite Like Query Optimization
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
[sqlite] SQLite Like Query Optimization
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
