Re: [sqlite] Feature Request - RowCount

2014-12-15 Thread Paul
> > On 14 Dec 2014, at 11:08am, Jean-Christophe Deschamps > wrote: > > > Without using slow triggers or changing the v3 file format there is still > > another possibility which could be implemented relatively easily. All it > > would need is a new pragma (or internal

Re: [sqlite] Feature Request - RowCount

2014-12-14 Thread RSmith
On 2014/12/13 21:46, James K. Lowden wrote: So the number of tools with feature X is no measure of the value of X. (Notable example: the tool should keep every query and result in a time-sequenced transcript log, so that prior results can be re-examined and prior queries modified. Most tools

Re: [sqlite] Feature Request - RowCount

2014-12-14 Thread Klaas V
Jim Callahan wrote:>#26 The unique columns have non-null values (the answer says a lot more,>but that is the essence of what I am relying on). Right, but the question was how to count rows as quickly as possible regarding any or all columnse.g. count(ProspectName) from Clients;  One can imagine

Re: [sqlite] Feature Request - RowCount

2014-12-14 Thread Jean-Christophe Deschamps
Hi Simon, A) In that sqlite_sequence table you mentioned, as an additional column. Always up-to-date. But sqlite_sequence isn't always created. AFAIK it only exists when one or more table exists with an integer primary key autoincrement. B) In the tables prepared by SQLite ANALYZE. If

Re: [sqlite] Feature Request - RowCount

2014-12-14 Thread Simon Slavin
On 14 Dec 2014, at 11:08am, Jean-Christophe Deschamps wrote: > Without using slow triggers or changing the v3 file format there is still > another possibility which could be implemented relatively easily. All it > would need is a new pragma (or internal function) like

Re: [sqlite] Feature Request - RowCount

2014-12-14 Thread Jean-Christophe Deschamps
At 03:14 14/12/2014, you wrote: ´¯¯¯ I take the point that the only possible improvements seem to need alteration to the file structure or added maintenance which may use up cycles for something that just isn't that important to DB use in general - and I have to agree, I too have zero want for

Re: [sqlite] Feature Request - RowCount

2014-12-13 Thread RSmith
On 2014/12/13 14:38, Richard Hipp wrote: The "SELECT count(*) FROM table" query already has a special optimization in the b-tree layer to make it go faster. You can see this by comparing the times of these queries: SELECT count(*) FROM table; SELECT count(*) FROM table WHERE 1;

Re: [sqlite] Feature Request - RowCount

2014-12-13 Thread David Empson
> On 14/12/2014, at 4:17 am, Simon Slavin wrote: > > > On 13 Dec 2014, at 12:38pm, Richard Hipp wrote: > >> Also, if there are indices available, SQLite attempts to count the smallest >> index (it has to guess at which is the smallest by looking at the

Re: [sqlite] Feature Request - RowCount

2014-12-13 Thread Simon Slavin
On 13 Dec 2014, at 7:46pm, James K. Lowden wrote: > Every DB Admin tool I've ever used proved to be more hinderance than > help. They seem to be written by the moderately competent to help the > novice, and run out of gas or fall over when faced with anything >

Re: [sqlite] Feature Request - RowCount

2014-12-13 Thread James K. Lowden
On Sat, 13 Dec 2014 14:15:15 +0200 RSmith wrote: > Most DB Admin tools out there displays the number of rows in a table > when you select it or open it, so too the one I am working on and > after testing stuff on Simon's question about the row counting, I > realised that

Re: [sqlite] Feature Request - RowCount

2014-12-13 Thread Richard Hipp
No. The fastest is to do "count(*)". -- D. Richard Hipp Sent from phone - Excuse brevity On Dec 13, 2014 11:13 AM, "Jim Callahan" wrote: > So, if I understand the discussion the fastest way to get a count from the > command line interface (CLI) is to count the

Re: [sqlite] Feature Request - RowCount

2014-12-13 Thread Jim Callahan
So, if I understand the discussion the fastest way to get a count from the command line interface (CLI) is to count the rows in the primary key, assuming you have a primary key and that it is not a composite key. SELECT COUNT(primarykey) FROM table1 The "primarykey" in the above example is a

Re: [sqlite] Feature Request - RowCount

2014-12-13 Thread Simon Slavin
On 13 Dec 2014, at 12:38pm, Richard Hipp wrote: > Also, if there are indices available, SQLite attempts to count the smallest > index (it has to guess at which is the smallest by looking at the number > and declared datatypes of the columns) and counting the smallest index >

Re: [sqlite] Feature Request - RowCount

2014-12-13 Thread Richard Hipp
The "SELECT count(*) FROM table" query already has a special optimization in the b-tree layer to make it go faster. You can see this by comparing the times of these queries: SELECT count(*) FROM table; SELECT count(*) FROM table WHERE 1; The WHERE clause on the second query disables