Re: [sqlite] Is is possible to optimize this query on a very large datatbase table?‏

2011-10-09 Thread Simon Slavin
On 10 Oct 2011, at 12:37am, Frank Chang wrote: > Simon Slavin, Here is the schema which I used. CREATE TABLE > [BlobLastNameTest] ([FieldName] CHAR (25), [Vertices] BLOB ) Okay. That's not what you posted originally. Okay so we have CREATE TABLE BlobLastNameTest (FieldName TEXT PRIMARY KEY,

Re: [sqlite] Is is possible to optimize this query on a very large datatbase table?‏

2011-10-09 Thread Frank Chang
Simon Slavin, Here is the schema which I used. CREATE TABLE [BlobLastNameTest] ([FieldName] CHAR (25), [Vertices] BLOB ) With this schema it is possible to have multiple rows with the same FieldName. This is intentional since I am writing a Windows and Linux C++ multithreaded application

Re: [sqlite] Is is possible to optimize this query on a very large datatbase table?

2011-10-09 Thread Simon Slavin
On 9 Oct 2011, at 4:13pm, Frank Chang wrote: > CREATE TABLE [BlobLastNameTest] ([FieldName] CHAR (25) PRIMARY KEY, > [Vertices] BLOB ) This form CREATE TABLE BlobLastNameTest (FieldName TEXT PRIMARY KEY, Vertices BLOB) does exactly the same in SQLite. >

Re: [sqlite] Is is possible to optimize this query on a very large datatbase table?

2011-10-09 Thread Florian Weimer
* Frank Chang: > This table could potentially hold 10 to 40 million rows. We are > using the following query to obtain the minumum rowid for each > unique LastName: > > sqlite> explain query plan select t1.FieldName,t1.rowid from BlobLastNameTest > t1 > GROUP BY t1.FIELDNAME HAVING t1.rowid =

Re: [sqlite] Is is possible to optimize this query on a very large datatbase table?

2011-10-09 Thread Igor Tandetnik
Frank Chang wrote: > Hi, We are using the following schema : > CREATE TABLE [BlobLastNameTest] ([FieldName] CHAR (25) PRIMARY KEY, > [Vertices] BLOB ) > > index|sqlite_autoindex_BlobLastNameTest_1|BlobLastNameTest|3| > > > This table could potentially hold 10 to 40

[sqlite] Is is possible to optimize this query on a very large datatbase table?

2011-10-09 Thread Frank Chang
Hi, We are using the following schema : CREATE TABLE [BlobLastNameTest] ([FieldName] CHAR (25) PRIMARY KEY, [Vertices] BLOB ) index|sqlite_autoindex_BlobLastNameTest_1|BlobLastNameTest|3| This table could potentially hold 10 to 40 million rows. We are using the following query to obtain