Simon Slavin, I tried your suggestion about creating the index:
>So do this:
>CREATE INDEX BLNTFieldName ON BlobLastNameTest (FieldName, rowid)
But sqlite complains that rowid is not a BLobLastNameTest column.
So then I tried your repeated selects. Your selects work fine but since we
are using Multiple concurrent Windows and Linux/pthread worker threads, these
repeated select resulrts would have to be processed in C++ code which would
overload each of the cores on the multicore CPU we are using. Instead, we were
hoping they we write a nice query to let the SQLite query processor do the
aggregation of BLOB(vertices) without incurring the full index scan cost.
Florian Weimer told us about this Posttgres feature DISTINCT ON which would
allow us to do this. However, SQLITE does not have DISTINCT ON yet, so the
Postgres thread recommended we use somerthing like to simulate DISTINCT ON:
select t1.FieldName,t1.rowid from BlobLastNameTest t1
where t1.rowid = (SELECT MIN(rowid) FROM
BlobLastNameTest
where FieldName =
t1.FIELDNAME)
order by t1.FieldName;
where the query plan looks like this:
0|0|0|SCAN TABLE BlobLastNameTest AS t1 USING COVERING INDEX
claramary5(~2709793 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE BlobLastNameTest USING COVERING INDEX claramary5 (FieldN
) (~1 rows)
Please let us know if there is a faster or more elegant way to this.
Finally, I tried your suggestion about : SELECT rowid,FieldName FROM
BlobLastNameTest GROUP BY FieldName ORDER BY rowid but it gives the wrong
answer where the right answer is the minimum rowid for a particular
FIELDNAME(i.e.LASTNAME). Your query runs very fast . Is it possible that you
could tweak it a litle so that it gives the right minimum ROWiD answer. Thank
you for all of your help.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
