Re: [sqlite] Is it possible to optimize this query on a very large database table

Igor Tandetnik, >> How come you only show one function? A user-defined aggregate function is >> actually represented by two C[++] functions - one that is called for every >> row >> and performs actual aggregation, and another that's called at the end of >> each >> group, reports the result

Re: [sqlite] Is it possible to optimize this query on a very large database table

Frank Chang 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

Re: [sqlite] Is it possible to optimize this query on a very large database table

Igor Tandetnik, The explain query plan for select FieldName, min(rowid) from BlobLastNameTest group by FieldName shows a full index scan, even after I run ANALYZE TABLE AND INDEX ON FIELDNAME, which could be problematic when the number of rows in BlobLastNameTest increases by a

Re: [sqlite] Is it possible to optimize this query on a very large database table Oct. 10, 2011 13:53:01 GMT

Igor Tandetnik, >>> Explain the problem you are trying to solve, *not* your proposed solution. >>> <<< What we are trying to achieve is to to find the minimum row id for each unique Field Name in BLobLastNameTest where many rows can have the same FIELDNAME but distinct

Re: [sqlite] Is it possible to optimize this query on a very large database table.

Frank Chang wrote: > Igor Tandetnik, > The fieldname groups in our BlobLastNameTable consist of > multiple rows where each pair of columns [FieldName, > BLOB[Vertices]] is unique. How so? You have FieldName declared as PRIMARY KEY. From

Re: [sqlite] Is it possible to optimize this query on a very large database table.

Igor Tandetnik, The fieldname groups in our BlobLastNameTable consist of multiple rows where each pair of columns [FieldName, BLOB[Vertices]] is unique. Therefore, every fieldname group does not just have a single row but instead 1000's or 1's rows. So that is why we use

Re: [sqlite] Is it possible to optimize a query on a very large database table?

Frank Chang wrote: > Florian Weimar and Igor Tadetnik, > > When I replace the GROUP BY t1.FIELDNAME with ORDER BY 1, > > select t1.FieldName,t1.rowid from BlobLastNameTest t1 where t1.r > owid = (SELECT MIN(rowid) FROM BlobLastNameTest where FieldName = >

Re: [sqlite] Is it possible to optimize a query on a very large database table?

Florian Weimar and Igor Tadetnik, When I replace the GROUP BY t1.FIELDNAME with ORDER BY 1, select t1.FieldName,t1.rowid from BlobLastNameTest t1 where t1.r owid = (SELECT MIN(rowid) FROM BlobLastNameTest where FieldName = t1.FIELDNAME) order by 1; the explain output seems to have 40%