Re: [sqlite] Why does LIKE operator affect order of query plan?

[Tim Romano]

Thanks once again.

If the optimizer is unaffected by the choice of  function to prepare the 
statement when a string literal is on the RHS,  I have to come up with 
another theory to explain why three different front-ends for SQLite 
report a query plan that differs from the one SQLite3.EXE reports for a 
simple query with string literal:

explain query plan
select * from myTable where myIndexedNOCASECollationVarcharColumn like 
'foo%'

SQLite3.exe  says it will use the index (and it does use it). The others 
all report  a full table scan will be carried out (and one is).  < 100ms 
versus 40 seconds.

Regards
Tim Romano

D. Richard Hipp wrote:
> On Nov 16, 2009, at 7:02 PM, Tim Romano wrote:
>
>   
>> Thanks for the reply. Sorry, I didn't make my question clear  
>> enough.   I
>> was trying to find out if the following statement would be true:
>>
>> If the value after the LIKE operator is a string literal (as distinct
>> from a bound parameter) then
>> the choice of function to compile the statement will have no effect on
>> the optimizer;
>> i.e. the optimizer is compiler-function-agnostic when the value after
>> the LIKE operator is a string literal.
>> 
>
> Correct.
>
>   
>> The LIKE optimization is not attempted if the right-hand side  
>> is a
>> parameter and the statement was prepared using sqlite3_prepare()  or
>> sqlite3_prepare16() . 
>>
>> 
>
>
> The reason for this is that if the RHS of the LIKE is a parameter, the  
> statement will need to be re-prepared whenever the parameter is re- 
> bound because SQLite has to know the actual text value of the RHS in  
> order to figure out if indices can be used or not.  But re-prepare is  
> not possible with sqlite3_prepare() and sqlite3_prepare16() since  
> those routine do not store the original SQL text.   
> sqlite3_prepare_v2() and sqlite3_prepare16_v2(), on the other hand, do  
> store the original SQL text and can re-prepare and so they can  
> optimize for parameters on the RHS of a LIKE.
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com 
> Version: 8.5.425 / Virus Database: 270.14.68/2507 - Release Date: 11/16/09 
> 19:53:00
>
>   

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

Re: [sqlite] Why does LIKE operator affect order of query plan?

[D. Richard Hipp]

On Nov 16, 2009, at 7:02 PM, Tim Romano wrote:

> Thanks for the reply. Sorry, I didn't make my question clear  
> enough.   I
> was trying to find out if the following statement would be true:
>
> If the value after the LIKE operator is a string literal (as distinct
> from a bound parameter) then
> the choice of function to compile the statement will have no effect on
> the optimizer;
> i.e. the optimizer is compiler-function-agnostic when the value after
> the LIKE operator is a string literal.

Correct.

>
> The LIKE optimization is not attempted if the right-hand side  
> is a
> parameter and the statement was prepared using sqlite3_prepare()  or
> sqlite3_prepare16() . 
>


The reason for this is that if the RHS of the LIKE is a parameter, the  
statement will need to be re-prepared whenever the parameter is re- 
bound because SQLite has to know the actual text value of the RHS in  
order to figure out if indices can be used or not.  But re-prepare is  
not possible with sqlite3_prepare() and sqlite3_prepare16() since  
those routine do not store the original SQL text.   
sqlite3_prepare_v2() and sqlite3_prepare16_v2(), on the other hand, do  
store the original SQL text and can re-prepare and so they can  
optimize for parameters on the RHS of a LIKE.

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Why does LIKE operator affect order of query plan?

[Tim Romano]

Thanks for the reply. Sorry, I didn't make my question clear enough.   I 
was trying to find out if the following statement would be true:

If the value after the LIKE operator is a string literal (as distinct 
from a bound parameter) then
the choice of function to compile the statement will have no effect on 
the optimizer;
i.e. the optimizer is compiler-function-agnostic when the value after 
the LIKE operator is a string literal.

The LIKE optimization is not attempted if the right-hand side is a 
parameter and the statement was prepared using sqlite3_prepare()  or 
sqlite3_prepare16() . 

Regards
Tim





D. Richard Hipp wrote:
> On Nov 16, 2009, at 5:14 PM, Tim Romano wrote:
>   
>> Do I understand the docs correctly, that if the query statement  
>> contains
>> a literal string (as distinct from a bound parameter) it doesn't  
>> matter
>> which function is used to compile the statement and the index WILL be
>> used provided all of the optimization prerequisites are met?
>>
>> 
>
>
> SQLite uses a cost-based query planner.  The query planner looks at  
> many (hundreds, thousands) of ways of doing the query and selects the  
> one it thinks will get the job done fastest.  If the LIKE term is what  
> seems likely (to SQLite) to give the fastest answer, then that is what  
> will be used.  And if conditions are as you describe, then probably  
> the LIKE term will seem fastest to SQLite.  But there are lots of  
> other considerations and hence no guarantees.
>
> One thing that SQLite does guarantee is that it will always make the  
> same query plan choice until you either (1) change the schema, (2) run  
> ANALYZE, (3) change the RHS of the LIKE operator, (4) compile with  
> SQLITE_ENABLE_STAT2 and change binding on other parameters in the  
> query, or (5) change SQLite release versions.  So once you get it  
> using LIKE it should always continue using LIKE.
>
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com 
> Version: 8.5.425 / Virus Database: 270.14.67/2506 - Release Date: 11/16/09 
> 07:43:00
>
>   

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

Re: [sqlite] Why does LIKE operator affect order of query plan?

[D. Richard Hipp]

On Nov 16, 2009, at 5:14 PM, Tim Romano wrote:
>
> Do I understand the docs correctly, that if the query statement  
> contains
> a literal string (as distinct from a bound parameter) it doesn't  
> matter
> which function is used to compile the statement and the index WILL be
> used provided all of the optimization prerequisites are met?
>


SQLite uses a cost-based query planner.  The query planner looks at  
many (hundreds, thousands) of ways of doing the query and selects the  
one it thinks will get the job done fastest.  If the LIKE term is what  
seems likely (to SQLite) to give the fastest answer, then that is what  
will be used.  And if conditions are as you describe, then probably  
the LIKE term will seem fastest to SQLite.  But there are lots of  
other considerations and hence no guarantees.

One thing that SQLite does guarantee is that it will always make the  
same query plan choice until you either (1) change the schema, (2) run  
ANALYZE, (3) change the RHS of the LIKE operator, (4) compile with  
SQLITE_ENABLE_STAT2 and change binding on other parameters in the  
query, or (5) change SQLite release versions.  So once you get it  
using LIKE it should always continue using LIKE.


D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Why does LIKE operator affect order of query plan?

[Tim Romano]

OK, the index IS being used with   ... where myCol LIKE 'foo%'  ... but 
only when I execute the query using the SQLite3.exe command-line 
utility.  But the index is not being used when the query is executed via 
three different front-ends to SQLite, all of which must be doing 
something to confound the index such as compiling the prepared statement 
with sqlite3_prepare().

Do I understand the docs correctly, that if the query statement contains 
a literal string (as distinct from a bound parameter) it doesn't matter 
which function is used to compile the statement and the index WILL be 
used provided all of the optimization prerequisites are met?

The LIKE optimization is not attempted if the right-hand side is a 
parameter  and the 
statement was prepared using sqlite3_prepare() 
 or sqlite3_prepare16() 
. 


Thanks
Tim


Igor Tandetnik wrote:
> Tim Romano wrote:
>   
>> Thanks for the correction, Pavel, about the mixed comparison mode
>> (ASCII-range: case-insensitive, above-ASCII: case-sensitive).
>>
>> I've added a column to my table:
>>
>> ALTER TABLE WORDS
>> ADD COLUMN spell varchar COLLATE NOCASE
>>
>> and have then copied the contents of a 100% pure ASCII column into
>> column SPELL.
>>
>> explain query plan
>> select * from WORDS where spell like 'foo%'
>>
>> shows that SQLite is still doing  full table scan.
>> 
>
> Did you create an index on that new column?
>
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com 
> Version: 8.5.425 / Virus Database: 270.14.67/2506 - Release Date: 11/16/09 
> 07:43:00
>
>   

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

Re: [sqlite] Why does LIKE operator affect order of query plan?

[Igor Tandetnik]

Tim Romano wrote:
> Thanks for the correction, Pavel, about the mixed comparison mode
> (ASCII-range: case-insensitive, above-ASCII: case-sensitive).
> 
> I've added a column to my table:
> 
> ALTER TABLE WORDS
> ADD COLUMN spell varchar COLLATE NOCASE
> 
> and have then copied the contents of a 100% pure ASCII column into
> column SPELL.
> 
> explain query plan
> select * from WORDS where spell like 'foo%'
> 
> shows that SQLite is still doing  full table scan.

Did you create an index on that new column?

Igor Tandetnik

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

Re: [sqlite] Why does LIKE operator affect order of query plan?

[Olaf Schmidt]

"Tim Romano"  schrieb im
Newsbeitrag news:4b017343.2040...@yahoo.com...

> I've added a column to my table:
>
> ALTER TABLE WORDS
> ADD COLUMN spell varchar COLLATE NOCASE
>
> and have then copied the contents of a 100% pure ASCII column
> into column SPELL.
>
> explain query plan
> select * from WORDS where spell like 'foo%'
>
> shows that SQLite is still doing  full table scan.
Did you create an Index on column "spell" beforehand?
(In that case, since you already gave the Collation in the
 Column-Def, you would not need to give the NoCase-
 Collation explicitely again in the Create Index-Statement).

> QUESTION:
> Is it possible to confirm that the column actually has been created
> with NOCASE collation?
Why not just test, what a simple Select gives as a result of e.g.:
  Select spell From Words Where spell = 'abc' Limit 1
In case column spell contains a "known word" as 'Abc' for
example, you should get back a resulting row - if NoCase
wouldn't have been recognized correctly, you shouldn't get
back a string-result (a resulting row) from that query (in such
a case-insensitivity-provocing Comparison-Attempt).

Olaf




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

Re: [sqlite] Why does LIKE operator affect order of query plan?

[Tim Romano]

Thanks for the correction, Pavel, about the mixed comparison mode 
(ASCII-range: case-insensitive, above-ASCII: case-sensitive).

I've added a column to my table:

ALTER TABLE WORDS
ADD COLUMN spell varchar COLLATE NOCASE

and have then copied the contents of a 100% pure ASCII column into 
column SPELL.

explain query plan
select * from WORDS where spell like 'foo%'

shows that SQLite is still doing  full table scan.  

QUESTION:
Is it possible to confirm that the column actually has been created with 
NOCASE collation?
PRAGMA table_info(WORDS) doesn't show the collation sequence for the 
column, and  PRAGMA collation_list  lists the names of sequences for the 
current connection but does not associate them with a column.

Thanks

 
sqlite3_prepare_v2()  or 
sqlite3_prepare16_v2() .
The LIKE optimization is not attempted if the right-hand side is a 
parameter  and the 
statement was prepared using sqlite3_prepare() 
 


Pavel Ivanov wrote:
>> collation-sequence is default NO-CASE
>> 
>
> Default collation is BINARY. So either yours is default or NOCASE - not both.
>
>   
>> A question, however, on the Latin-1, ASCII range requirement:  this is a
>> column requirement and not a database requirement, correct?
>> 
>
> It's not a requirement at all. It's just the fact that LIKE will
> compare ASCII characters case-insensitive and all other characters
> case-sensitive when case-sensitive comparison is off.
>
> Pavel
>
> On Mon, Nov 16, 2009 at 7:47 AM, Tim Romano  wrote:
>   
>> After reading  http://www.sqlite.org/optoverview.html,  I think my query
>> meets the requirements for index use with the LIKE operator:
>>
>> The column is varchar(75) and so TEXT affinity.
>> The column uses Latin-1 characters exclusively.
>> The wildcard appears at the far right end of the string literal, e.g.
>> myColumn LIKE 'foo%'
>> The escape clause does not appear.
>> Case-sensitivity=false;
>> collation-sequence is default NO-CASE
>>
>> QUESTION:
>> A question, however, on the Latin-1, ASCII range requirement:  this is a
>> column requirement and not a database requirement, correct?  I have
>> several columns with text affinity; one is strict ASCII and represents
>> characters outside the ASCII range as html-entities (e.g. "ü") and
>> the others store the unicode characters. The database encoding is UTF-8.
>>
>> My query with the LIKE operator worked instantaneously in MS-Access,
>> BTW, where I originally had the database. After exporting to delimited
>> text and reimporting into SQLite, most queries in SQLite are just as
>> fast, executing in under a second. But this query with the LIKE operator
>> takes 40 seconds because of the full-table scan.
>>
>> 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
> 
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com 
> Version: 8.5.425 / Virus Database: 270.14.67/2506 - Release Date: 11/16/09 
> 07:43:00
>
>   

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

Re: [sqlite] Why does LIKE operator affect order of query plan?

[Olaf Schmidt]

"Tim Romano"  schrieb im
Newsbeitrag news:4b0149c9.8000...@yahoo.com...
> ...
> My query with the LIKE operator worked instantaneously
> in MS-Access,  BTW, where I originally had the database.
Since Access *.mdbs are often used with(in) VB- or
VBA-based applications - are you by any chance
using my COM-wrapper (dhRichClient3.dll) to work with
SQLite?
If that is the case, the wrapper overrides the original
Like-function of the sqlite-engine, to perform Unicode-
aware comparisons on Windows-WStrings - and it also
tries its own "optimizations" in case you've passed an
"indexable" Like-comparison-string (with a '...%' at the
right-hand-side) in your Select.

Your index on the text-column in question would have to be
created (and "attributed") with ... Collate NoCase, to make
the "indexed Like optimizations" work as expected.

Olaf




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

Re: [sqlite] Why does LIKE operator affect order of query plan?

[Pavel Ivanov]

> collation-sequence is default NO-CASE

Default collation is BINARY. So either yours is default or NOCASE - not both.

> A question, however, on the Latin-1, ASCII range requirement:  this is a
> column requirement and not a database requirement, correct?

It's not a requirement at all. It's just the fact that LIKE will
compare ASCII characters case-insensitive and all other characters
case-sensitive when case-sensitive comparison is off.

Pavel

On Mon, Nov 16, 2009 at 7:47 AM, Tim Romano  wrote:
> After reading  http://www.sqlite.org/optoverview.html,  I think my query
> meets the requirements for index use with the LIKE operator:
>
> The column is varchar(75) and so TEXT affinity.
> The column uses Latin-1 characters exclusively.
> The wildcard appears at the far right end of the string literal, e.g.
> myColumn LIKE 'foo%'
> The escape clause does not appear.
> Case-sensitivity=false;
> collation-sequence is default NO-CASE
>
> QUESTION:
> A question, however, on the Latin-1, ASCII range requirement:  this is a
> column requirement and not a database requirement, correct?  I have
> several columns with text affinity; one is strict ASCII and represents
> characters outside the ASCII range as html-entities (e.g. "ü") and
> the others store the unicode characters. The database encoding is UTF-8.
>
> My query with the LIKE operator worked instantaneously in MS-Access,
> BTW, where I originally had the database. After exporting to delimited
> text and reimporting into SQLite, most queries in SQLite are just as
> fast, executing in under a second. But this query with the LIKE operator
> takes 40 seconds because of the full-table scan.
>
> 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

Re: [sqlite] Why does LIKE operator affect order of query plan?

[Tim Romano]

After reading  http://www.sqlite.org/optoverview.html,  I think my query 
meets the requirements for index use with the LIKE operator:

The column is varchar(75) and so TEXT affinity.
The column uses Latin-1 characters exclusively.
The wildcard appears at the far right end of the string literal, e.g. 
myColumn LIKE 'foo%'
The escape clause does not appear.
Case-sensitivity=false;
collation-sequence is default NO-CASE

QUESTION:
A question, however, on the Latin-1, ASCII range requirement:  this is a 
column requirement and not a database requirement, correct?  I have 
several columns with text affinity; one is strict ASCII and represents 
characters outside the ASCII range as html-entities (e.g. "") and 
the others store the unicode characters. The database encoding is UTF-8.

My query with the LIKE operator worked instantaneously in MS-Access, 
BTW, where I originally had the database. After exporting to delimited 
text and reimporting into SQLite, most queries in SQLite are just as 
fast, executing in under a second. But this query with the LIKE operator 
takes 40 seconds because of the full-table scan.

Thanks


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

Re: [sqlite] Why does LIKE operator affect order of query plan?

[Shane Harrelson]

On Sun, Nov 15, 2009 at 11:41 AM, P Kishor  wrote:
> On Sun, Nov 15, 2009 at 10:39 AM, Tim Romano  wrote:
>> I have a query with joined inline views that runs in about 100ms against
>> a 4 million row table joined to a 275,000 row table.  Not bad, SQLite :-)
>>
>> But when I use the LIKE operator instead of the = operator, the order of
>> the query plan changes, though the same indexes are involved, and the
>> query takes 40 seconds.  I'm trying to figure out what, if anything, I
>> can do to guide SQLite here.
>>
>> In broad terms, what is it about the use of the LIKE operator that
>> causes SQLite to re-order the plan, and is there any way to guide?
>>
>
> LIKE doesn't use indexes, although there are tricks that these SQL
> gurus will probably tell that could help you with workarounds. LIKE
> does a full scan.
>
>

There are certain conditions in which a LIKE (or GLOB) term will be
transformed by the query planner into an equivalent expression to
allow the use of indices.  Please see
http://www.sqlite.org/optoverview.html for a more in-depth discussion.


>> And what does the "from" column in the explain plan results refer to?
>> Are the values the tables/relations in the query statement? If so, how
>> are  they mapped? In order of appearance in the statement, so that 0 is
>> the first table mentioned in the statement?
>>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why does LIKE operator affect order of query plan?

[P Kishor]

On Sun, Nov 15, 2009 at 1:19 PM, Tim Romano  wrote:
> Thanks for the reply.
>
> A follow question:   I can understand why ... myColumn LIKE "%foo%"
> ...   would have to do a full scan but shouldn't  ...myColumn LIKE
> "foo%" ...  be able to use an index?
>
>

see Section 4 in http://www.sqlite.org/optoverview.html for all your
questions re. LIKE.



> P Kishor wrote:
>> On Sun, Nov 15, 2009 at 10:39 AM, Tim Romano  wrote:
>>
>>> I have a query with joined inline views that runs in about 100ms against
>>> a 4 million row table joined to a 275,000 row table.  Not bad, SQLite :-)
>>>
>>> But when I use the LIKE operator instead of the = operator, the order of
>>> the query plan changes, though the same indexes are involved, and the
>>> query takes 40 seconds.  I'm trying to figure out what, if anything, I
>>> can do to guide SQLite here.
>>>
>>> In broad terms, what is it about the use of the LIKE operator that
>>> causes SQLite to re-order the plan, and is there any way to guide?
>>>
>>>
>>
>> LIKE doesn't use indexes, although there are tricks that these SQL
>> gurus will probably tell that could help you with workarounds. LIKE
>> does a full scan.
>>
>>
>>
>>> And what does the "from" column in the explain plan results refer to?
>>> Are the values the tables/relations in the query statement? If so, how
>>> are  they mapped? In order of appearance in the statement, so that 0 is
>>> the first table mentioned in the statement?
>>>
>>> Thanks
>>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>
>>
>>
>>
>> 
>>
>>
>> No virus found in this incoming message.
>> Checked by AVG - www.avg.com
>> Version: 8.5.425 / Virus Database: 270.14.66/2504 - Release Date: 11/15/09 
>> 07:50:00
>>
>>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, Wisconsin, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why does LIKE operator affect order of query plan?

[Tim Romano]

Thanks for the reply.

A follow question:   I can understand why ... myColumn LIKE "%foo%" 
...   would have to do a full scan but shouldn't  ...myColumn LIKE 
"foo%" ...  be able to use an index?


P Kishor wrote:
> On Sun, Nov 15, 2009 at 10:39 AM, Tim Romano  wrote:
>   
>> I have a query with joined inline views that runs in about 100ms against
>> a 4 million row table joined to a 275,000 row table.  Not bad, SQLite :-)
>>
>> But when I use the LIKE operator instead of the = operator, the order of
>> the query plan changes, though the same indexes are involved, and the
>> query takes 40 seconds.  I'm trying to figure out what, if anything, I
>> can do to guide SQLite here.
>>
>> In broad terms, what is it about the use of the LIKE operator that
>> causes SQLite to re-order the plan, and is there any way to guide?
>>
>> 
>
> LIKE doesn't use indexes, although there are tricks that these SQL
> gurus will probably tell that could help you with workarounds. LIKE
> does a full scan.
>
>
>   
>> And what does the "from" column in the explain plan results refer to?
>> Are the values the tables/relations in the query statement? If so, how
>> are  they mapped? In order of appearance in the statement, so that 0 is
>> the first table mentioned in the statement?
>>
>> Thanks
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>> 
>
>
>
>   
> 
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com 
> Version: 8.5.425 / Virus Database: 270.14.66/2504 - Release Date: 11/15/09 
> 07:50:00
>
>   

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

Re: [sqlite] Why does LIKE operator affect order of query plan?

[P Kishor]

On Sun, Nov 15, 2009 at 10:39 AM, Tim Romano  wrote:
> I have a query with joined inline views that runs in about 100ms against
> a 4 million row table joined to a 275,000 row table.  Not bad, SQLite :-)
>
> But when I use the LIKE operator instead of the = operator, the order of
> the query plan changes, though the same indexes are involved, and the
> query takes 40 seconds.  I'm trying to figure out what, if anything, I
> can do to guide SQLite here.
>
> In broad terms, what is it about the use of the LIKE operator that
> causes SQLite to re-order the plan, and is there any way to guide?
>

LIKE doesn't use indexes, although there are tricks that these SQL
gurus will probably tell that could help you with workarounds. LIKE
does a full scan.


> And what does the "from" column in the explain plan results refer to?
> Are the values the tables/relations in the query statement? If so, how
> are  they mapped? In order of appearance in the statement, so that 0 is
> the first table mentioned in the statement?
>
> Thanks
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, Wisconsin, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users