Re: [sqlite] Query problems

2013-09-04 Thread Keith Medcalf
> 1. If you define a column with NOCASE and later an index without, it > won't be possible to insert two values differing only by case, > because the column will reject it. Of course it will accept the value, unless you declared the column unique so that a unique index is created using the

Re: [sqlite] Query problems

2013-09-04 Thread James K. Lowden
On Tue, 3 Sep 2013 23:50:09 +0200 Eduardo Morras wrote: > Don't know if column collation overrides index collation or viceversa. It's probably simpler to think of them as two things, table and index. Neither "overrides" the other. Ideally, they use the same collation. In

Re: [sqlite] Query problems

2013-09-03 Thread Eduardo Morras
On Mon, 2 Sep 2013 13:48:02 +0100 Simon Slavin wrote: > > On 2 Sep 2013, at 8:25am, Eduardo Morras wrote: > > > Or create the index with collate > > > > CREATE INDEX idx_collated_column ON myTable ( column COLLATE NOCASE ) > > The problem with doing

Re: [sqlite] Query problems

2013-09-02 Thread Joseph L. Casale
> Plus, of course, index will only ever be used for operations where you have > overridden the default collating sequence for the operation, for example by > specifying collate nocase in the join expression, or adding the collate > nocase to > the order by or group by. I assume this explains why

Re: [sqlite] Query problems

2013-09-02 Thread Keith Medcalf
age- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Simon Slavin > Sent: Monday, 2 September, 2013 06:48 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Query problems > > > On 2 Sep 2013, at 8:25am, Eduard

Re: [sqlite] Query problems

2013-09-02 Thread Simon Slavin
On 2 Sep 2013, at 8:25am, Eduardo Morras wrote: > Or create the index with collate > > CREATE INDEX idx_collated_column ON myTable ( column COLLATE NOCASE ) The problem with doing it in the index is that it's hard to predict when SQLite will use a particular index. Better

Re: [sqlite] Query problems

2013-09-02 Thread Eduardo Morras
On Mon, 2 Sep 2013 02:12:05 +0100 Simon Slavin wrote: > > On 2 Sep 2013, at 2:03am, Joseph L. Casale wrote: > > > I am using LIKE as the columns are indexed NOCASE and I need the > > comparison case insensitive. > > Have you tried using '=' ?

Re: [sqlite] Query problems

2013-09-01 Thread Joseph L. Casale
> Have you tried using '=' ? > > Also if you declare the columns as COLLATE NOCASE in your table definition, > then using '=' will definitely work the way you want it to. An example would > be > > CREATE TABLE myTable (myName TEXT COLLATE NOCASE) Simon, That took this query from not finishing

Re: [sqlite] Query problems

2013-09-01 Thread Joseph L. Casale
> > 0 0 1 SCAN TABLE d_table_b AS da (~10 rows) > > > > Is this the index you referenced in you reply to Simon? > Maybe you are using wrong index/column? I'll recheck, I am also reading up on indexes as they relate to optimizing queries. Could be I made a mistake. > I had

Re: [sqlite] Query problems

2013-09-01 Thread Joseph L. Casale
> LIKE is used when comparing strings with wildcards. For example, val LIKE > 'abra%' (which will match 'abraCaDAbra' and 'abrakadee'. > > If there are no wildcards you should be using =, not LIKE. LIKE will/should > always indicate that a table or index scan is required, perhaps of the whole >

Re: [sqlite] Query problems

2013-09-01 Thread Igor Korot
Hi, Joseph, On Sun, Sep 1, 2013 at 6:21 PM, Joseph L. Casale wrote: > > Hi, > > Can you do "DESCRIBE QUERY PLAN " and post results here? > > > > Also, what do you mean by "unbearable at scale"? Did you measure it? What > > is the result? > > > > Thank you. > > It

Re: [sqlite] Query problems

2013-09-01 Thread Joseph L. Casale
> Have you tried using '=' ? > > Also if you declare the columns as COLLATE NOCASE in your table definition, > then using '=' will definitely work the way you want it to. An example would > be > > CREATE TABLE myTable (myName TEXT COLLATE NOCASE) > > Simon. I did and it excluded the

Re: [sqlite] Query problems

2013-09-01 Thread Keith Medcalf
> I am using LIKE as the columns are indexed NOCASE and I need the > comparison case insensitive. I suspect this is where is breaks down > but I don't know enough sql to really appreciate the ways I could > approach this better. LIKE is used when comparing strings with wildcards. For example,

Re: [sqlite] Query problems

2013-09-01 Thread Joseph L. Casale
> Hi, > Can you do "DESCRIBE QUERY PLAN " and post results here? > > Also, what do you mean by "unbearable at scale"? Did you measure it? What > is the result? > > Thank you. It doesn't finish with maybe 4 or 5 hours run time. Sorry, do you mean "explain query plan ..."? 0 0 1

Re: [sqlite] Query problems

2013-09-01 Thread Simon Slavin
On 2 Sep 2013, at 2:03am, Joseph L. Casale wrote: > I am using LIKE as the columns are indexed NOCASE and I need the > comparison case insensitive. Have you tried using '=' ? Also if you declare the columns as COLLATE NOCASE in your table definition, then using '='

Re: [sqlite] Query problems

2013-09-01 Thread Igor Korot
Hi, Can you do "DESCRIBE QUERY PLAN " and post results here? Also, what do you mean by "unbearable at scale"? Did you measure it? What is the result? Thank you. On Sun, Sep 1, 2013 at 6:03 PM, Joseph L. Casale wrote: > I have a query that is unbearable at scale, for

[sqlite] Query problems

2013-09-01 Thread Joseph L. Casale
I have a query that is unbearable at scale, for example when s_table_a and s_table_b have 70k and 1.25M rows. SELECT s.id AS s_id ,s.lid AS s_lid ,sa.val AS s_sid ,d.id AS d_id ,d.lid AS d_lid FROM s_table_b sa JOIN d_table_b da ON ( da.key=sa.key

Re: [sqlite] Query Problems - Keyword? Encoding?

2011-09-29 Thread Eric Anderson
On Thu, Sep 29, 2011 at 8:42 PM, David Garfield wrote: > If the value in key is a blob, then like matches it and = does not. Thanks. This suggestion helped me track down the issue. I was actually going through ActiveRecord (the ORM for Ruby on Rails). Was upgrading

Re: [sqlite] Query Problems - Keyword? Encoding?

2011-09-29 Thread Jay A. Kreibich
On Thu, Sep 29, 2011 at 08:32:04PM -0400, Eric Anderson scratched on the wall: > The below statement returns records: > > SELECT * FROM recordings WHERE "key" LIKE > '4df0247ce1a97685a782d2cb051b48ed952e666c'; > > But this one does not: > > SELECT * FROM recordings WHERE "key" = >

Re: [sqlite] Query Problems - Keyword? Encoding?

2011-09-29 Thread David Garfield
If the value in key is a blob, then like matches it and = does not. Because like has to do a string conversion on key, it also doesn't use the index. Try: SELECT * FROM recordings WHERE "key" = cast('4df0247ce1a97685a782d2cb051b48ed952e666c' as blob); Or try inserting the key as text in the

[sqlite] Query Problems - Keyword? Encoding?

2011-09-29 Thread Eric Anderson
The below statement returns records: SELECT * FROM recordings WHERE "key" LIKE '4df0247ce1a97685a782d2cb051b48ed952e666c'; But this one does not: SELECT * FROM recordings WHERE "key" = '4df0247ce1a97685a782d2cb051b48ed952e666c'; The only difference is that = and LIKE have been swapped. I