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. > index|sqlite_autoindex_BlobLastNameTest_1|BlobLastNameTest|3| Is this the same as CREATE INDEX sqlite_autoindex_BlobLastNameTest_1 ON BlobLastNameTest (Vertices) ? Now sure where the '3' comes from. > 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 = (SELECT MIN(rowid) FROM > BlobLastNameTes > t where FieldName = t1.FIELDNAME); As you guessed, this is hideously inefficient. But there is a problem. You have defined your FieldName column as a primary key. This means there cannot be any two rows with the same FieldName. This makes your query pointless. Do you actually want to have multiple rows with the same FieldName ? If so let SQLite use rowid as the PRIMARY KEY (which is what it does if you don't define one yourself). If not, please clarify your search. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
