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

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

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

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)

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

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

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

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

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

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

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

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

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

"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

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

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

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

"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

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

> 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

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

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,

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

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

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

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

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

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

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

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

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

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