Re: [sqlite] using index when using concatination

[Igor Tandetnik]

Black, Michael (IS)  wrote:
> 3.7.4 doesn't indicate it will use an index in either case with like...but 
> glob seems to call the index...why does glob use the
> index but like does not? 

LIKE is case-insensitive by default, while GLOB is case-sensitive. Thus, LIKE 
works with NOCASE collation while GLOB wants BINARY collation. The document I 
cited discusses all this in detail.
-- 
Igor Tandetnik

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

Re: [sqlite] using index when using concatination

[Black, Michael (IS)]

I upgraded to 3.7.1.1 and tested this...glob works wonderfully...especially 
when there are few hits it makes a huge difference since it uses the index.

I don't see why like shouldn't be using the index in these cases as that's what 
the docs appear to say it should do.  A%A meets the qualifications for it.

I had a dictionary of 1M word phrases I used.



sqlite> pragma cache_size=20;
sqlite> .separator ','
sqlite> create table words(id integer,word text);
sqlite> .import 100.dat words
sqlite> create index idx on words(word);
sqlite> .timer on
sqlite> select count(*) from words where word glob 'P*P';
4241
CPU Time: user 0.25 sys 0.00

sqlite> select count(*) from words where word like 'P%P';
4241
CPU Time: user 0.328125 sys 0.00

sqlite> select count(*) from words where word like 'A%A';
0
CPU Time: user 0.281250 sys 0.00

sqlite> select count(*) from words where word like 'Z%Z';
0
CPU Time: user 0.281250 sys 0.00

sqlite> select count(*) from words where word glob 'Z*Z';
0
CPU Time: user 0.00 sys 0.00

sqlite> select count(*) from words where word glob 'L*P';
136
CPU Time: user 0.015625 sys 0.00

sqlite> select count(*) from words where word like 'L%P';
136
CPU Time: user 0.281250 sys 0.00



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Black, Michael (IS) [michael.bla...@ngc.com]
Sent: Sunday, July 10, 2011 12:03 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] using index when using concatination

3.7.4 doesn't indicate it will use an index in either case with like...but glob 
seems to call the index...why does glob use the index but like does not?





sqlite> create table words(word text);
sqlite> create index idx on words(word);
sqlite> explain query plan select * from words where "word" like 'a' || '%' || 
'a';
sele  order  from  deta
  -    
0 0  0 SCAN TABLE words (~50 rows)
sqlite> explain query plan select * from words where word like 'a%a';
sele  order  from  deta
  -    
0 0  0 SCAN TABLE words (~50 rows)
sqlite> explain query plan select * from words where word like 'a%';
sele  order  from  deta
  -    
0 0  0 SCAN TABLE words (~50 rows)
sqlite> explain query plan select * from words where word glob 'a%';
sele  order  from  deta
  -    
0 0  0 SEARCH TABLE words USING COVERING INDEX idx (word>? 
AND word explain query plan select * from words where word glob 'a%b';
sele  order  from  deta
  -    
0 0  0 SEARCH TABLE words USING COVERING INDEX idx (word>? 
AND word explain query plan select * from words where "word" glob 'a' || '%' || 
'a';
sele  order  from  deta
  -    
0 0  0 SCAN TABLE words (~50 rows)



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Igor Tandetnik [itandet...@mvps.org]
Sent: Sunday, July 10, 2011 9:22 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] using index when using concatination

Baruch Burstein <bmburst...@gmail.com> wrote:
> I am using the following SQL statement for SQLite:
>
>select * from words where "word" like ? || '%' || ? ;
>
> In order to bind parameters to the first and last letters. I have tested
> this both with and without an index on the column `word`, and the results
> are the same. However, when running the queries as
>
>select * from words where "word" like 'a%a';
>
> etc. (that is, hardcoding each value instead of using ||, the query is about
> x10 faster when indexed.

LIKE comparison may use the index only under certain very limited circumstances 
- see section 4 at

http://sqlite.org/optoverview.html

> Can someone show me how to use both the index and the parameters?

Michael Black's suggestion should work: make it

where word like ?;

build the string of the form "a%b" in your application, and bind it to the 
parameter. Alternatively, you could try something like this:

where word >= :first and word < :next and word like '%' || :last;

Then bind :first as the first letter, :next as the character that immediately 
follows :first in the collation (e.g. if :first is 'a', then :next is 'b'), and 
:last is the last letter.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlit

Re: [sqlite] using index when using concatination

[Black, Michael (IS)]

3.7.4 doesn't indicate it will use an index in either case with like...but glob 
seems to call the index...why does glob use the index but like does not?





sqlite> create table words(word text);
sqlite> create index idx on words(word);
sqlite> explain query plan select * from words where "word" like 'a' || '%' || 
'a';
sele  order  from  deta
  -    
0 0  0 SCAN TABLE words (~50 rows)
sqlite> explain query plan select * from words where word like 'a%a';
sele  order  from  deta
  -    
0 0  0 SCAN TABLE words (~50 rows)
sqlite> explain query plan select * from words where word like 'a%';
sele  order  from  deta
  -    
0 0  0 SCAN TABLE words (~50 rows)
sqlite> explain query plan select * from words where word glob 'a%';
sele  order  from  deta
  -    
0 0  0 SEARCH TABLE words USING COVERING INDEX idx (word>? 
AND word explain query plan select * from words where word glob 'a%b';
sele  order  from  deta
  -    
0 0  0 SEARCH TABLE words USING COVERING INDEX idx (word>? 
AND word explain query plan select * from words where "word" glob 'a' || '%' || 
'a';
sele  order  from  deta
  -    
0 0  0 SCAN TABLE words (~50 rows)



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Igor Tandetnik [itandet...@mvps.org]
Sent: Sunday, July 10, 2011 9:22 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] using index when using concatination

Baruch Burstein <bmburst...@gmail.com> wrote:
> I am using the following SQL statement for SQLite:
>
>select * from words where "word" like ? || '%' || ? ;
>
> In order to bind parameters to the first and last letters. I have tested
> this both with and without an index on the column `word`, and the results
> are the same. However, when running the queries as
>
>select * from words where "word" like 'a%a';
>
> etc. (that is, hardcoding each value instead of using ||, the query is about
> x10 faster when indexed.

LIKE comparison may use the index only under certain very limited circumstances 
- see section 4 at

http://sqlite.org/optoverview.html

> Can someone show me how to use both the index and the parameters?

Michael Black's suggestion should work: make it

where word like ?;

build the string of the form "a%b" in your application, and bind it to the 
parameter. Alternatively, you could try something like this:

where word >= :first and word < :next and word like '%' || :last;

Then bind :first as the first letter, :next as the character that immediately 
follows :first in the collation (e.g. if :first is 'a', then :next is 'b'), and 
:last is the last letter.
--
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] using index when using concatination

[Igor Tandetnik]

Baruch Burstein  wrote:
> I am using the following SQL statement for SQLite:
> 
>select * from words where "word" like ? || '%' || ? ;
> 
> In order to bind parameters to the first and last letters. I have tested
> this both with and without an index on the column `word`, and the results
> are the same. However, when running the queries as
> 
>select * from words where "word" like 'a%a';
> 
> etc. (that is, hardcoding each value instead of using ||, the query is about
> x10 faster when indexed.

LIKE comparison may use the index only under certain very limited circumstances 
- see section 4 at

http://sqlite.org/optoverview.html

> Can someone show me how to use both the index and the parameters?

Michael Black's suggestion should work: make it

where word like ?;

build the string of the form "a%b" in your application, and bind it to the 
parameter. Alternatively, you could try something like this:

where word >= :first and word < :next and word like '%' || :last;

Then bind :first as the first letter, :next as the character that immediately 
follows :first in the collation (e.g. if :first is 'a', then :next is 'b'), and 
:last is the last letter.
-- 
Igor Tandetnik

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

Re: [sqlite] using index when using concatination

[Igor Tandetnik]

Igor Tandetnik  wrote:
> Black, Michael (IS)  wrote:
>> Any particular reason you can't build your own string and just pass one 
>> parameter?
>> 
>> select * from words where "word" like ?;
> 
> That won't help - SQLite still can't use the index.

I take this back. From http://sqlite.org/optoverview.html section 4: "The 
right-hand side of the LIKE or GLOB must be either a string literal or a 
parameter bound to a string literal that does not begin with a wildcard 
character." So it's at least possible to use the index here, if all other 
conditions also hold.
-- 
Igor Tandetnik

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

Re: [sqlite] using index when using concatination

[Igor Tandetnik]

Black, Michael (IS)  wrote:
> Any particular reason you can't build your own string and just pass one 
> parameter?
> 
> select * from words where "word" like ?;

That won't help - SQLite still can't use the index.

> And any reason why you don't create a 2nd field holds the 1st and last char? 
> and index that? Then your query should be blazingly
> fast as it will actually use the index (which I don't believe LIKE uses at 
> all). 

It may, under certain limited circumstances.
-- 
Igor Tandetnik

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

Re: [sqlite] using index when using concatination

[Black, Michael (IS)]

Any particular reason you can't build your own string and just pass one 
parameter?



select * from words where "word" like ?;

And any reason why you don't create a 2nd field holds the 1st and last char? 
and index that? Then your query should be blazingly fast as it will actually 
use the index (which I don't believe LIKE uses at all).



Though it is curious as to why the speed difference...does sqlite build the 
like string on every compare or such?



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Baruch Burstein [bmburst...@gmail.com]
Sent: Sunday, July 10, 2011 7:27 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] using index when using concatination

I am using the following SQL statement for SQLite:

select * from words where "word" like ? || '%' || ? ;

In order to bind parameters to the first and last letters. I have tested
this both with and without an index on the column `word`, and the results
are the same. However, when running the queries as

select * from words where "word" like 'a%a';

etc. (that is, hardcoding each value instead of using ||, the query is about
x10 faster when indexed.

Can someone show me how to use both the index and the parameters?
___
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