Re: [sqlite] LIKE Query with ESCAPE character

[Richard Hipp]

On 2/27/19, julian robichaux  wrote:
> Am I doing something wrong here, or perhaps misunderstanding the
> documentation? My expectation is that both LIKE queries will use the
> index, but the EXPLAIN QUERY PLAN results tell me something different.

There was an issue with the LIKE optimization when there was an ESCAPE
clause and the PRAGMA case_sensitive_like=ON setting was in effect.
That particular combination of circumstances should work, but it did
not.  All the other combinations are fine.

The fix is here: https://www.sqlite.org/src/info/6ae4b8c525f446dd

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] LIKE Query with ESCAPE character

[julian robichaux]

Hi, I hope this is a simple question that someone can answer without a lot 
of trouble (maybe I am simply misunderstanding something).

According to https://www.sqlite.org/optoverview.html#the_like_optimization 
, a LIKE query can use an ESCAPE character and still be optimized as long 
as the character is single-byte. According to 
https://www.sqlite.org/changes.html#version_3_21_0 this was added in 
version 3.21.0.

I am a C++ programmer currently using version 3.24.0.

I set PRAGMA case_sensitive_like=1 so my LIKE queries are not 
case-sensitive. My table has a column named 'path' that is set as UNIQUE, 
so the column is auto-indexed. When I run this:

EXPLAIN QUERY PLAN SELECT * FROM MyTable WHERE path LIKE 'a%'

I get this as a return value:

SEARCH TABLE MyTable USING INDEX sqlite_autoindex_MyTable (path>? AND 
pathhttp://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[Dominique Devienne]

On Wed, Feb 27, 2013 at 3:16 PM, Igor Tandetnik  wrote:

> On 2/27/2013 4:35 AM, Dominique Devienne wrote:
>
>> PS: Something else that should also be part of SQLite built-in is the
>> optimization that col LIKE 'prefix%' queries should implicitly try to use
>> an index on col.
>>
>
> http://www.sqlite.org/**optoverview.html#like_opt


Thanks for the reminder. Note though that last time I checked [1], this
didn't work for a multi-column index, even if the column involved in a
prefix-based like-where-clause is first in the index. --DD

[1]
http://stackoverflow.com/questions/11152371/how-to-improve-the-performance-of-query-with-where-and-group-by-in-sqlite/11736532#11736532
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[Igor Tandetnik]

On 2/27/2013 4:35 AM, Dominique Devienne wrote:

PS: Something else that should also be part of SQLite built-in is the
optimization that col LIKE 'prefix%' queries should implicitly try to use
an index on col.


http://www.sqlite.org/optoverview.html#like_opt

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[Dominique Devienne]

On Wed, Feb 27, 2013 at 11:23 AM, Clemens Ladisch wrote:

> Dominique Devienne wrote:
> > My $0.02 is that such a chr() function could/should be built-in to
> SQLite.
>
> Apparently, drh has a time machine:
> http://www.sqlite.org/cgi/src/info/209b21085b
>

Indeed! Spooky :) --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[Clemens Ladisch]

Dominique Devienne wrote:
> My $0.02 is that such a chr() function could/should be built-in to SQLite.

Apparently, drh has a time machine:
http://www.sqlite.org/cgi/src/info/209b21085b


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[Dominique Devienne]

On Tue, Feb 26, 2013 at 2:31 PM, Clemens Ladisch  wrote:

>   ... 'somedata/' || CAST(x'F48FBFBF' AS TEXT)
>

Great trick! But it hardly qualifies as user friendly though, no?

For our app, I added a chr() SQL function that take an arbitrary number of
integers and UTF-8 encodes them:

register_function(-1, "chr", codepoint_to_utf8);

so the above becomes

... 'somedata/' || chr(1114111)

Of course, the fact that it's a decimal code-point number is not ideal
since less expressive than

... 'somedata/' || chr(0x10)

but hexa-literals are not supported in SQL it seems (I tried just SQLite
and Oracle).

My $0.02 is that such a chr() function could/should be built-in to SQLite.
--DD

PS: Something else that should also be part of SQLite built-in is the
optimization that col LIKE 'prefix%' queries should implicitly try to use
an index on col. I suspect it may be more difficult than I expect because
of collation, but absent custom collations, I wish that optimization was
available.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[Jay A. Kreibich]

On Tue, Feb 26, 2013 at 12:34:03PM +, Simon Slavin scratched on the wall:
> On 26 Feb 2013, at 7:39am, dd  wrote:

> >   This database has unicode strings(chinese/japanese/...etc strings). can
> > you tell me which is the correct character to replace with z?
> 
> Ah.  There you have a problem because internally SQLite does not
> handle language support within Unicode characters.  I'm going to let
> someone with SQLite/Unicode expertise answer this one, but it may be
> that with Unicode even your LIKE command would not have worked
> properly and you should use something like

  The only issue there is that the default case-insensitive nature of
  LIKE won't work.  Otherwise LIKE should have no problems with
  matching unicode strings.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[Igor Tandetnik]

On 2/26/2013 9:25 AM, dd wrote:

Igor/Clemen Ladisch,


SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND "somedata/z"


I want to replace z with 10 character. But, it's failed.


Failed in what way? How do you run your query? Show your code.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[Igor Tandetnik]

On 2/26/2013 9:18 AM, dd wrote:

10 decimal value is 1114111. But, some chinese characters are greater
than this value.


You are mistaken. There are no Unicode characters above U+10, 
whether Chinese or otherwise.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[dd]

Igor/Clemen Ladisch,

>>SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND "somedata/z"

I want to replace z with 10 character. But, it's failed. what is the
correct decimal value for that?


On Tue, Feb 26, 2013 at 6:18 PM, dd  wrote:

> 10 decimal value is 1114111. But, some chinese characters are greater
> than this value. Is it correct character(10) to replace with z?
>
> Please correct me if I am doing wrong.
>
>
> On Tue, Feb 26, 2013 at 5:58 PM, Igor Tandetnik wrote:
>
>> On 2/26/2013 8:31 AM, Clemens Ladisch wrote:
>>
>>> Igor Tandetnik wrote:> On 2/26/2013 2:39 AM, dd wrote:
>>>
 SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND
> "somedata/zzz"
>
> This database has unicode strings(chinese/japanese/...**etc strings).
> can
> you tell me which is the correct character to replace with z?
>

 U+, of course.

>>>
>>> Unicode characters can have more than 16 bits, of course.
>>>
>>
>> ... but SQLite orders them with simple memcmp (absent a custom
>> collation), so 0x will still compare greater than any surrogate pair.
>>
>> If the database file uses UTF-8 encoding, and contains supplemental
>> characters, then yes, a UTF-8 representation of U+10 would be prudent.
>> --
>> Igor Tandetnik
>>
>>
>> __**_
>> 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] like query

[dd]

10 decimal value is 1114111. But, some chinese characters are greater
than this value. Is it correct character(10) to replace with z?

Please correct me if I am doing wrong.


On Tue, Feb 26, 2013 at 5:58 PM, Igor Tandetnik  wrote:

> On 2/26/2013 8:31 AM, Clemens Ladisch wrote:
>
>> Igor Tandetnik wrote:> On 2/26/2013 2:39 AM, dd wrote:
>>
>>> SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND
 "somedata/zzz"

 This database has unicode strings(chinese/japanese/...**etc strings).
 can
 you tell me which is the correct character to replace with z?

>>>
>>> U+, of course.
>>>
>>
>> Unicode characters can have more than 16 bits, of course.
>>
>
> ... but SQLite orders them with simple memcmp (absent a custom collation),
> so 0x will still compare greater than any surrogate pair.
>
> If the database file uses UTF-8 encoding, and contains supplemental
> characters, then yes, a UTF-8 representation of U+10 would be prudent.
> --
> Igor Tandetnik
>
>
> __**_
> 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] like query

[Igor Tandetnik]

On 2/26/2013 8:31 AM, Clemens Ladisch wrote:

Igor Tandetnik wrote:> On 2/26/2013 2:39 AM, dd wrote:

SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND "somedata/zzz"

This database has unicode strings(chinese/japanese/...etc strings). can
you tell me which is the correct character to replace with z?


U+, of course.


Unicode characters can have more than 16 bits, of course.


... but SQLite orders them with simple memcmp (absent a custom 
collation), so 0x will still compare greater than any surrogate pair.


If the database file uses UTF-8 encoding, and contains supplemental 
characters, then yes, a UTF-8 representation of U+10 would be prudent.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[Clemens Ladisch]

Igor Tandetnik wrote:> On 2/26/2013 2:39 AM, dd wrote:
>> SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND "somedata/zzz"
>>
>> This database has unicode strings(chinese/japanese/...etc strings). can
>> you tell me which is the correct character to replace with z?
>
> U+, of course.

Unicode characters can have more than 16 bits, of course.

RFC 3629 restricts UTF-8-encoded characters to U+10.
In SQL, that would be:

  ... 'somedata/' || CAST(x'F48FBFBF' AS TEXT)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[Igor Tandetnik]

On 2/26/2013 2:39 AM, dd wrote:

>>SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND
"somedata/zzz"

This database has unicode strings(chinese/japanese/...etc strings). can
you tell me which is the correct character to replace with z?


U+, of course.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[Simon Slavin]

On 26 Feb 2013, at 7:39am, dd  wrote:

>>> SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND
>>> "somedata/zzz"
> 
>   This database has unicode strings(chinese/japanese/...etc strings). can
> you tell me which is the correct character to replace with z?

Ah.  There you have a problem because internally SQLite does not handle 
language support within Unicode characters.  I'm going to let someone with 
SQLite/Unicode expertise answer this one, but it may be that with Unicode even 
your LIKE command would not have worked properly and you should use something 
like

SELECT * FROM emp WHERE substr(column_test,1,9) = 'somedata/'

Which will slower because it will not be able to use an index for optimization. 
 But it might be faster than the LIKE because using a regexp for matching seems 
likely to me to be slower than substr().

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[dd]

   >>SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND
"somedata/zzz"

   This database has unicode strings(chinese/japanese/...etc strings). can
you tell me which is the correct character to replace with z?




On Mon, Feb 25, 2013 at 8:13 PM, Simon Slavin  wrote:

>
> On 25 Feb 2013, at 2:46pm, dd  wrote:
>
> >  Table has string data type column. format of strings:
> > somedata1/somedata2/somedata3
> >
> >  I have written query to search : select * from emp where column_test
> like
> > "somedata/%";
> >
> >  It gives perfomance as per articles in internet. Is it? If yes, what is
> > alternate query for this?
>
> If the format of your 'LIKE' clause is always that you have fixed text at
> the beginning, then you can speed up your search a lot.  Create an index on
> the 'column_test' field, and use this query:
>
> SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND "somedata/zzz"
>
> replace 'zzz' with '~~~' or something similar if you're being really fussy.
>
> Simon.
> ___
> 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] like query

[dd]

Thanks Richard.


On Mon, Feb 25, 2013 at 6:54 PM, Richard Hipp  wrote:

> On Mon, Feb 25, 2013 at 9:46 AM, dd  wrote:
>
> > Hi,
> >
> >   Table has string data type column. format of strings:
> > somedata1/somedata2/somedata3
> >
> >   I have written query to search : select * from emp where column_test
> like
> > "somedata/%";
> >
> >   It gives perfomance as per articles in internet. Is it? If yes, what is
> > alternate query for this?
> >
>
> The query might go faster if you do:
>
> CREATE INDEX emp_idx1 ON emp(column_test COLLATE nocase);
>
> Or, if you really intended to do a case-sensitive search, you could say:
>
> SELECT * FROM emp WHERE column_test GLOB 'somedata/*';
>
>
>
>
>
> >
> >   Thanks in advance.
> >
> > Best Regards,
> > dd.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] like query

[Simon Slavin]

On 25 Feb 2013, at 2:46pm, dd  wrote:

>  Table has string data type column. format of strings:
> somedata1/somedata2/somedata3
> 
>  I have written query to search : select * from emp where column_test like
> "somedata/%";
> 
>  It gives perfomance as per articles in internet. Is it? If yes, what is
> alternate query for this?

If the format of your 'LIKE' clause is always that you have fixed text at the 
beginning, then you can speed up your search a lot.  Create an index on the 
'column_test' field, and use this query:

SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND "somedata/zzz"

replace 'zzz' with '~~~' or something similar if you're being really fussy.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like query

[Richard Hipp]

On Mon, Feb 25, 2013 at 9:46 AM, dd  wrote:

> Hi,
>
>   Table has string data type column. format of strings:
> somedata1/somedata2/somedata3
>
>   I have written query to search : select * from emp where column_test like
> "somedata/%";
>
>   It gives perfomance as per articles in internet. Is it? If yes, what is
> alternate query for this?
>

The query might go faster if you do:

CREATE INDEX emp_idx1 ON emp(column_test COLLATE nocase);

Or, if you really intended to do a case-sensitive search, you could say:

SELECT * FROM emp WHERE column_test GLOB 'somedata/*';





>
>   Thanks in advance.
>
> Best Regards,
> dd.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] like query

[dd]

Hi,

  Table has string data type column. format of strings:
somedata1/somedata2/somedata3

  I have written query to search : select * from emp where column_test like
"somedata/%";

  It gives perfomance as per articles in internet. Is it? If yes, what is
alternate query for this?

  Thanks in advance.

Best Regards,
dd.
___
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]

 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

[drh]

"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

[P Kishor]

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

[Kalyani Phadke]

 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

[P Kishor]

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

[P Kishor]

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

[Kalyani Phadke]

 
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

[drh]

"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

[Evans, Mark (Tandem)]

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

[BareFeet]

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

[drh]

"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

[Ken]

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

[Kalyani Phadke]

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] Special characters handling in SQLite Like Query

[Kalyani Phadke]

 
If I have data as follows, they wont show up when I try to search for
them.=20 [[[ ]]]=20

[EMAIL PROTECTED]&*=20

()_+|{}:=14<>?=20

-=3D\[];',./=20

@@@ %%%=20

### ^^^=20

___---=20

Ho_mer=20

[EMAIL PROTECTED]&*()_+|`{}:=14<>?-=3D\[];=12,./=20

_Storage-Room=20

@ # $=20

_ - +=20

% ^ &=20

asdf_qwerty=20

As all the data contains special charcters ,I am replacing them with
SearchS =3D Trim(SearchS)=20

SearchS =3D Replace(SearchS, "'", "''")=20

SearchS =3D Replace(SearchS, "[", "[[]")=20

SearchS =3D Replace(SearchS, "%", "[%]")=20

SearchS =3D Replace(SearchS, "^", "[^]")=20

SearchS =3D Replace(SearchS, "#", "[#]")=20

SearchS =3D Replace(SearchS, "_", "[_]")=20

My SQL Query looks like this select * from table_name where name LIKE
'%" & SearchS & "%'=20

eg . If I am trying to search for '%%%' my query will be=20

select * from table_name where name LIKE '% [%][%][%]%'=20

Which is returning zero results.=20

so how should I replace special characters for SQlite.=20

Thanks

-
To unsubscribe, send email to [EMAIL PROTECTED]
-