Re: [sqlite] Index question

On 28 Apr 2013, at 10:18pm, Paolo Bolzoni wrote: > Interesting, so sqlite3 is smart enough to actually move the blob instead > of copying and deleting? If it is the case it is indeed great. SQLite3 keeps all the data for a row together on disk. It rewrites the

Re: [sqlite] Index question

Interesting, so sqlite3 is smart enough to actually move the blob instead of copying and deleting? If it is the case it is indeed great. On Sun, Apr 28, 2013 at 5:12 PM, Simon Slavin wrote: > > On 28 Apr 2013, at 3:51pm, Paolo Bolzoni >

Re: [sqlite] Index question

I use only the C API. The function causing it in my program is: fprintf(stderr, "%s\n", sqlite3_errmsg(db)); and I think it comes out from: sqlite3.c:70718: zErr = sqlite3MPrintf(db, "cannot open %s column for writing", zFault); On Sun, Apr 28, 2013 at 6:05 PM, Richard Hipp

Re: [sqlite] Index question

On Sun, Apr 28, 2013 at 10:02 AM, Paolo Bolzoni < paolo.bolzoni.br...@gmail.com> wrote: > I get this error: "cannot > open indexed column for writing." > > What does it mean? > > That error is not coming from SQLite. Are you using a wrapper program of some kind - or perhaps a third-party query

Re: [sqlite] Index question

On 28 Apr 2013, at 3:51pm, Paolo Bolzoni wrote: > So I should write my BLOB in another (not-indexed) table, UPDATE the > indexed table copying from the other, > and finally delete the line in the first table? All in one transaction? That would work and would be a

Re: [sqlite] Index question

So I should write my BLOB in another (not-indexed) table, UPDATE the indexed table copying from the other, and finally delete the line in the first table? All in one transaction? On Sun, Apr 28, 2013 at 4:23 PM, Simon Slavin wrote: > > On 28 Apr 2013, at 3:02pm, Paolo

Re: [sqlite] Index question

On 28 Apr 2013, at 3:02pm, Paolo Bolzoni wrote: > And it seems quite an improvement, alas now I get this error: "cannot > open indexed column for writing." You have a column of type BLOB. It is now an indexed column. You are trying to use the BLOB editing

Re: [sqlite] Index question

Sorry, it seems gmail messed up the layout just before sending. On Sun, Apr 28, 2013 at 4:02 PM, Paolo Bolzoni wrote: > I was playing with indexes, I started checking one my queries with > EXPLAIN QUERY PLAN and > I got this result: > selectidorder from

[sqlite] Index question

I was playing with indexes, I started checking one my queries with EXPLAIN QUERY PLAN and I got this result: selectidorder fromdetail -- -- -- - 0 0 1

Re: [sqlite] Index question about index

On 14 Jul 2011, at 5:37pm, Black, Michael (IS) wrote: > Would glob be faster than like? Since I assume like has to case-convert? Given your SELECT string: >> select * from tsamov where tsamov_code like 'AFG%' If you want speed don't use either, use SELECT * FROM tsamov WHERE tsamov_code

Re: [sqlite] Index question about index

Would glob be faster than like? Since I assume like has to case-convert? And you don't have to set any pragmas or NOCASE for it to use the index. select * from tsamov where tsamov_code glob 'AFG*'; sqlite> explain query plan select * from tsamov where tsamov_code glob 'AFG*'; sele order

Re: [sqlite] Index question about index

On 7/14/2011 12:01 PM, Pavel Ivanov wrote: >> could the next query use the tsamov_code index ??: >> select * from tsamov where tsamov_code like 'AFG%' > > Only after > pragma case_sensitive_like = true; ... or if the index uses COLLATE NOCASE clause. -- Igor Tandetnik

Re: [sqlite] Index question about index

> could the next query use the tsamov_code index ??: >    select * from tsamov where tsamov_code like 'AFG%' Only after pragma case_sensitive_like = true; Read more about it here: http://www.sqlite.org/optoverview.html#like_opt. Pavel On Thu, Jul 14, 2011 at 11:56 AM, Sebastian Bermudez

[sqlite] Index question about index

simple question: i have table tsamov create table tsamov ( tsamov_id integer not null primary key ,tsamov_descri text, ,tsamov_code text ) i have index on tsamov_code (it's varchar column of lenght=5) could the next query use the tsamov_code index ??: select * from tsamov where

Re: [sqlite] index question

Sorry, my emailer messed things up, try it again 0OpenVirtual10keyinfo(1,BINARY) 1Goto031 2Integer00 3OpenRead02 4SetNumColumns02 5Integer00 6OpenRead23keyinfo(1,BINARY) 7Integer1

Re: Re[2]: [sqlite] index question

FYI: If you have a very small number of rows in the table and index will make it slower, rather than faster. On 11/21/05, Wilfried Mestdagh <[EMAIL PROTECTED]> wrote: > Hi Bert, > > >>'select distinct Name ' + > >> 'from Rx ' + > >> 'where RxDT >= ' + DT + ' ' + > >> 'order by Name' > > One

Re: [sqlite] index question

Wilfried Mestdagh wrote: Hi, I'm a little confused how to create indexes. (beginners question, but I could not find or understeand clearly in FAQ). I have on a table following 3 selects: 'select distinct Name ' + 'from Rx ' + 'where RxDT >= ' + DT + ' ' + 'order by Name' 'select * ' +

[sqlite] index question

Hi, I'm a little confused how to create indexes. (beginners question, but I could not find or understeand clearly in FAQ). I have on a table following 3 selects: 'select distinct Name ' + 'from Rx ' + 'where RxDT >= ' + DT + ' ' + 'order by Name' 'select * ' + 'from Rx ' + 'where RxDt

Re[2]: [sqlite] Index question

On Wed, 27 Oct 2004, Taka Muraoka wrote: >UP> Hi Taka, > >>> >>> These are stored in tables like this: >>> >>> CREATE TABLE object >>> ( >>> object_id INTEGER PRIMARY KEY >>> ) ; >>> >>> CREATE TABLE item >>> ( >>> object_id INTEGER , >>> seq_no INTEGER