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 report
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
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 n
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
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 confoun
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 10
"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
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 S
"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
> 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
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.
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 i
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
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 j
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 c
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
16 matches
Mail list logo