Re: [sqlite] select count(*) does not use primary key??

2013-06-07 Thread Richard Hipp
On Fri, Jun 7, 2013 at 11:51 AM, Ryan Johnson wrote: > On 06/06/2013 10:52 AM, Gabriel Corneanu wrote: > >> In my opinion, count(*) is the same as count(rowid) (I see that even >> count() is accepted); I could say it's even the same as count(x) (any >> other >>

Re: [sqlite] select count(*) does not use primary key??

2013-06-07 Thread Ryan Johnson
On 06/06/2013 10:52 AM, Gabriel Corneanu wrote: In my opinion, count(*) is the same as count(rowid) (I see that even count() is accepted); I could say it's even the same as count(x) (any other field). Not quite... count(x) only counts rows having non-NULL x. Granted, that's not a problem for

Re: [sqlite] select count(*) does not use primary key??

2013-06-07 Thread Eduardo Morras
On Fri, 07 Jun 2013 13:12:14 +0200 Clemens Ladisch wrote: > Eduardo Morras wrote: > > where t.a = NULL > > where t.a IS NULL > > (NULL compares as not equal to any value, including itself.) OPppss you're right. Thought too fast and wrote even faster :( > > Regards, >

Re: [sqlite] select count(*) does not use primary key??

2013-06-07 Thread Clemens Ladisch
Eduardo Morras wrote: > where t.a = NULL where t.a IS NULL (NULL compares as not equal to any value, including itself.) Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] select count(*) does not use primary key??

2013-06-07 Thread Eduardo Morras
On Thu, 6 Jun 2013 10:53:55 -0400 Richard Hipp wrote: > On Thu, Jun 6, 2013 at 10:52 AM, Gabriel Corneanu > wrote: > > > Strange is, count(*) uses the cover index for a but "select count(a)" does > > NOT use the same cover index... > > > > count(a)

Re: [sqlite] select count(*) does not use primary key??

2013-06-07 Thread Gabriel Corneanu
This is not at all my case ... I don't obviously write 1 by 1, but using blocks of data ( array of struct ), virtual tables wrappers, and "insert ... select". This way I can achieve >200k rec/s, or at least 100k when having some more fields. Right now I'm completely CPU bound, it's 100% load

Re: [sqlite] select count(*) does not use primary key??

2013-06-07 Thread Simon Slavin
On 7 Jun 2013, at 8:57am, Gabriel Corneanu wrote: > BTW I found this by opening some file over network, which of course made > everything worse. > [...] > Not that I really need, but I have to support specified data rates up to 100k > records / second. Maximum

Re: [sqlite] select count(*) does not use primary key??

2013-06-07 Thread Gabriel Corneanu
I admit I didn't think (or didn't even read in detail) about technical implementation. This is an extract from analyzer: *** Table AE_DATA Percentage of total database.. 99.89% Number of entries. 1030371 Bytes of

Re: [sqlite] select count(*) does not use primary key??

2013-06-06 Thread Igor Tandetnik
On 6/6/2013 11:26 AM, Gabriel Corneanu wrote: Again sorry for count(a), I wrote too fast. I understand of course about null values. Otherwise by rowid I mean the autogenerated primary key. In my actual case, I have a field as alias. CREATE TABLE t(id integer primary key, a); explain query plan

Re: [sqlite] select count(*) does not use primary key??

2013-06-06 Thread Richard Hipp
On Thu, Jun 6, 2013 at 11:26 AM, Gabriel Corneanu wrote: > > 2. Is there NO WAY to quickly get the row count WITHOUT full scan if I > only have the auto primary key?? > The b-tree structures in the SQLite file format do not store the row count, as that slows down

Re: [sqlite] select count(*) does not use primary key??

2013-06-06 Thread Clemens Ladisch
Gabriel Corneanu wrote: > I was surprised to find that simple query "select count(*) from table" took > too much time, and found that it does NOT use the primary key index?? In SQLite, indexes are stored as B-trees, ordered by the indexed columns. Tables are _also_ stored as B-trees, ordered by

Re: [sqlite] select count(*) does not use primary key??

2013-06-06 Thread Gabriel Corneanu
Again sorry for count(a), I wrote too fast. I understand of course about null values. Otherwise by rowid I mean the autogenerated primary key. In my actual case, I have a field as alias. CREATE TABLE t(id integer primary key, a); explain query plan select count(*) from t -> scan table

Re: [sqlite] select count(*) does not use primary key??

2013-06-06 Thread Gabriel Corneanu
OK I understand, then it remains the question why it does not use the primary key?? Thanks, Gabriel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select count(*) does not use primary key??

2013-06-06 Thread Jay A. Kreibich
On Thu, Jun 06, 2013 at 04:52:12PM +0200, Gabriel Corneanu scratched on the wall: > I was surprised to find that simple query "select count(*) from table" took > too much time, and found that it does NOT use the primary key index?? > e.g. > CREATE TABLE t(a); > explain query plan select count(*)

Re: [sqlite] select count(*) does not use primary key??

2013-06-06 Thread Richard Hipp
On Thu, Jun 6, 2013 at 10:52 AM, Gabriel Corneanu wrote: > Strange is, count(*) uses the cover index for a but "select count(a)" does > NOT use the same cover index... > count(a) has to check for NULL values of a, which are not counted. count(*) does not. -- D.

[sqlite] select count(*) does not use primary key??

2013-06-06 Thread Gabriel Corneanu
I was surprised to find that simple query "select count(*) from table" took too much time, and found that it does NOT use the primary key index?? e.g. CREATE TABLE t(a); explain query plan select count(*) from t I get : SCAN TABLE t (~100 rows) If I use CREATE TABLE t(a unique), then it uses