Re: [sqlite] Problems using AUTOINCREMENT row IDs in indexes

> I've banged on about this problem and thanks to your assistance it > seems > to be resolved now. Perhaps you could update the documentation at > http://www.sqlite.org/autoinc.html and > http://www.sqlite.org/lang_createindex.html - I had certainly read > these many times so a note about

Re: [sqlite] Problems using AUTOINCREMENT row IDs in indexes

> > > Any comments on this - Richard or Dan? > > > > The INTEGER PRIMARY KEY is always included in every index as an > > implied extra column on the end. If you explicitly add the > > INTEGER PRIMARY KEY as a column in the index, then you have it > > in the index twice, which serves no

Re: [sqlite] Problems using AUTOINCREMENT row IDs in indexes

On Thu, 20 Nov 2008 15:25:35 +0100, Christophe Leske <[EMAIL PROTECTED]> wrote in General Discussion of SQLite Database : > >>> Any chance to spare the ID field and get an index on the rowid for a >>> given table? >> I do not understand the question. Please rephrase.

Re: [sqlite] Problems using AUTOINCREMENT row IDs in indexes

>> Any chance to spare the ID field and get an index on the rowid for a >> given table? > I do not understand the question. Please rephrase. Use more words. > Most of the ID fields are primary integer keys which also coincident with the value of the rowid for a given table. Yes, you

Re: [sqlite] Problems using AUTOINCREMENT row IDs in indexes

On Nov 13, 2008, at 9:56 AM, Hugh Gibson wrote: > > This query: > > SELECT * FROM Signals WHERE sUserID='[EMAIL PROTECTED]' AND nRowID >= > 1932308 > and nRowID <= 1932508 Try either of these: SELECT * FROM Signals WHERE +sUserID='...' AND nRowID>=... AND nRowid<=; SELECT *

Re: [sqlite] Problems using AUTOINCREMENT row IDs in indexes

> > Any comments on this - Richard or Dan? > > The INTEGER PRIMARY KEY is always included in every index as an > implied extra column on the end. If you explicitly add the INTEGER > PRIMARY KEY as a column in the index, then you have it in the > index twice, which serves no purpose I

Re: [sqlite] Problems using AUTOINCREMENT row IDs in indexes

On Nov 20, 2008, at 7:17 AM, Christophe Leske wrote: > >> The INTEGER PRIMARY KEY is always included in every index as an >> implied extra column on the end. If you explicitly add the INTEGER >> PRIMARY KEY as a column in the index, then you have it in the index >> twice, which serves no

Re: [sqlite] Problems using AUTOINCREMENT row IDs in indexes

> The INTEGER PRIMARY KEY is always included in every index as an > implied extra column on the end. If you explicitly add the INTEGER > PRIMARY KEY as a column in the index, then you have it in the index > twice, which serves no purpose but does confuse the optimizer. Don't > do that.

Re: [sqlite] Problems using AUTOINCREMENT row IDs in indexes

On Nov 20, 2008, at 6:20 AM, Hugh Gibson wrote: > Any comments on this - Richard or Dan? The INTEGER PRIMARY KEY is always included in every index as an implied extra column on the end. If you explicitly add the INTEGER PRIMARY KEY as a column in the index, then you have it in the index

Re: [sqlite] Problems using AUTOINCREMENT row IDs in indexes

Any comments on this - Richard or Dan? Hugh > This appears to be the same as > http://www.sqlite.org/cvstrac/tktview?tn=1893 > > I've attached a comment there. > > This is quite a big trap which I only found when loading up the > database with a lot of data. What's the timescale for fixing

Re: [sqlite] Problems using AUTOINCREMENT row IDs in indexes

This appears to be the same as http://www.sqlite.org/cvstrac/tktview?tn=1893 I've attached a comment there. This is quite a big trap which I only found when loading up the database with a lot of data. What's the timescale for fixing the bug? I will have to bypass the AUTOINCREMENT functionality

[sqlite] Problems using AUTOINCREMENT row IDs in indexes

I'm having problems getting good index choice in SQLite 3.6.4 for a field which is INTEGER PRIMARY KEY AUTOINCREMENT. I've got the following table: CREATE TABLE Signals (sSignalID Text DEFAULT '',sText Text DEFAULT '',sTime Text DEFAULT '',sUserID Text DEFAULT '',nRowID INTEGER PRIMARY KEY