Frank Chang <frank_chan...@hotmail.com> wrote: > The explain query plan for select FieldName, min(rowid) from > BlobLastNameTest group by FieldName shows a full index scan.
Of course. How else do you expect to be able to look at every FieldName? You seem to be expecting some kind of a "compressed" index - an index with as many entried as there are distinct values of FieldName, each entry pointing to... I'm not sure what, exactly. There ain't no such thing. An index on FieldName still has as many entries as there are rows in the underlying table - it's just sorted by FieldName. To get a list of all distinct values of FieldName, SQLite has to scan this index, and simply discard any value that is equal to that from previous row. If you think you need such a "compressed index", you would have to maintain it yourself, as a separate table. Personally, based on the description of your problem, I don't think you need any such thing. > Here is how I might do the update: > > 1. CREATE TABLE FOO(FIELDNAME CHAR(25), IDROW INT); > 2 INSERT INTO FOO select FieldName, min(rowid) from BlobLastNameTest > group by FieldName > 3. INSERT OR REPLACE INTO BLOBLASTNAMETEST SELECT t1.FIELDNAME, > UDF(t1.ROWID,t1.FIELDNAME,this,'BLOBLASTNAMETEST') FROM > FOO WHERE BLOBLASTNAMETEST.FIELDNAME = FOO.FIELDNAME AND > BLOBLASTNAMETEST.ROWID = FOO.IDROW. This last query makes no sense to me. It refers to BLOBLASTNAMETEST in WHERE clause though it was never mentioned in FROM clause. It refers to identifiers "t1" and "this" that were never declared. How about this? Drop steps 1 and 2, and run this statement instead: insert or replace into BlobLastNameTest(rowid, FieldName, Vertices) select min(rowid), FieldName, MyAccumulation(Vertices) from BlobLastNameTest group by FieldName; where MyAccumulation is a custom aggregate function that does whatever you mean by "accumulate". This way, you should be able to do everything in a single pass. See http://sqlite.org/c3ref/create_function.html , the description of xStep and xFinal parameters, for an explanation of how to set up a custom aggregate function. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
