Re: [sqlite] Is there a performance difference between COUNT(*) and COUNT(name)

[John Found]

On Tue, 5 Sep 2017 23:31:32 +0200
Cecil Westerhof  wrote:

> 2017-09-05 23:11 GMT+02:00 Simon Slavin :
> 
> >
> >
> > On 5 Sep 2017, at 9:21pm, Cecil Westerhof  wrote:
> >
> > > I want to know the number of teas I have in stock. For this I use:
> > > SELECT COUNT(Tea)
> > > FROM   teaInStock
> > >
> > > Tea cannot be NULL, so this is the same as:
> > > SELECT COUNT(*)
> > > FROM   teaInStock
> > >
> > > ​But I find the first more clear.
> > > I almost always see the second variant. Is this because it is more
> > > efficient, or are people just ‘lazy’?​
> >
> > Your guess is right !
> >
> > To do COUNT(*) SQLite has to retrieve all the rows.
> > To do COUNT(value) has to retrieve all the rows and test the value of each
> > row to make sure it is not NULL.
> >
> > Also, SQLite has a specific piece of code which makes COUNT(*) more
> > efficient than counting the values.  However, unless you have a big
> > database, the difference for your case may be small.  If you find
> > COUNT(Tea) easier to understand perhaps you should use that one.
> >
> 
> ​I will keep using COUNT(Tea) then, but keep in the back of my mind that I
> maybe should change that if a table becomes big.

In my tests even on small tables count(colName) is at least 2 times slower than
count(*), even if both queries uses covering indexes. So, using count(colName) 
has
meaning only if you really want to count only not null rows.

Making exception for columns that "never contain NULL" in the name of "source 
clearness" actually is hard for detection hidden bug that can strike after long 
time on the database schema change.


> 
> Thanks.​
> 
> -- 
> Cecil Westerhof
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


-- 
http://fresh.flatassembler.net
http://asm32.info
John Found 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a performance difference between COUNT(*) and COUNT(name)

[Cecil Westerhof]

2017-09-05 23:11 GMT+02:00 Simon Slavin :

>
>
> On 5 Sep 2017, at 9:21pm, Cecil Westerhof  wrote:
>
> > I want to know the number of teas I have in stock. For this I use:
> > SELECT COUNT(Tea)
> > FROM   teaInStock
> >
> > Tea cannot be NULL, so this is the same as:
> > SELECT COUNT(*)
> > FROM   teaInStock
> >
> > ​But I find the first more clear.
> > I almost always see the second variant. Is this because it is more
> > efficient, or are people just ‘lazy’?​
>
> Your guess is right !
>
> To do COUNT(*) SQLite has to retrieve all the rows.
> To do COUNT(value) has to retrieve all the rows and test the value of each
> row to make sure it is not NULL.
>
> Also, SQLite has a specific piece of code which makes COUNT(*) more
> efficient than counting the values.  However, unless you have a big
> database, the difference for your case may be small.  If you find
> COUNT(Tea) easier to understand perhaps you should use that one.
>

​I will keep using COUNT(Tea) then, but keep in the back of my mind that I
maybe should change that if a table becomes big.

Thanks.​

-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a performance difference between COUNT(*) and COUNT(name)

[Simon Slavin]

On 5 Sep 2017, at 9:21pm, Cecil Westerhof  wrote:

> I want to know the number of teas I have in stock. For this I use:
> SELECT COUNT(Tea)
> FROM   teaInStock
> 
> Tea cannot be NULL, so this is the same as:
> SELECT COUNT(*)
> FROM   teaInStock
> 
> ​But I find the first more clear.
> I almost always see the second variant. Is this because it is more
> efficient, or are people just ‘lazy’?​

Your guess is right !

To do COUNT(*) SQLite has to retrieve all the rows.
To do COUNT(value) has to retrieve all the rows and test the value of each row 
to make sure it is not NULL.

Also, SQLite has a specific piece of code which makes COUNT(*) more efficient 
than counting the values.  However, unless you have a big database, the 
difference for your case may be small.  If you find COUNT(Tea) easier to 
understand perhaps you should use that one.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a performance difference between COUNT(*) and COUNT(name)

[John McKown]

On Tue, Sep 5, 2017 at 3:21 PM, Cecil Westerhof 
wrote:

> I want to know the number of teas I have in stock. For this I use:
> SELECT COUNT(Tea)
> FROM   teaInStock
>
> Tea cannot be NULL, so this is the same as:
> SELECT COUNT(*)
> FROM   teaInStock
>
> ​But I find the first more clear.
> I almost always see the second variant. Is this because it is more
> efficient, or are people just ‘lazy’?​
>

​The first seems more efficient. Example using EXPLAIN:

sqlite> create table data (tea text);
sqlite> insert into data(tea) values("Lampsang Souchung");
sqlite> insert into data(tea) values("Keemun");
sqlite> explain select count(*) from data;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 7 000  Start at 7
1 OpenRead   1 2 0 1  00  root=2 iDb=0
2 Count  1 1 000  r[1]=count()
3 Close  1 0 000
4 Copy   1 2 000  r[2]=r[1]
5 ResultRow  2 1 000  output=r[2]
6 Halt   0 0 000
7 Transaction0 0 1 0  01  usesStmtJournal=0
8 TableLock  0 2 0 data   00  iDb=0 root=2
write=0
9 Goto   0 1 000
sqlite> explain select count(tea) from data;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 12000  Start at 12
1 Null   0 1 200  r[1..2]=NULL
2 OpenRead   0 2 0 1  00  root=2 iDb=0; data
3 Rewind 0 7 000
4   Column 0 0 300  r[3]=data.tea
5   AggStep0   0 3 1 count(1)   01  accum=r[1]
step(r[3])
6 Next   0 4 001
7 Close  0 0 000
8 AggFinal   1 1 0 count(1)   00  accum=r[1] N=1
9 Copy   1 4 000  r[4]=r[1]
10ResultRow  4 1 000  output=r[4]
11Halt   0 0 000
12Transaction0 0 1 0  01  usesStmtJournal=0
13TableLock  0 2 0 data   00  iDb=0 root=2
write=0
14Goto   0 1 000
sqlite> drop table data;
sqlite> -- see if NOT NULL makes a difference
sqlite> create table data(tea text not null);
sqlite> insert into data(tea) values("Lapsang Souchung");
sqlite> insert into data(tea) values("Keemun");
sqlite> explain select count(*) from data;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 7 000  Start at 7
1 OpenRead   1 2 0 1  00  root=2 iDb=0
2 Count  1 1 000  r[1]=count()
3 Close  1 0 000
4 Copy   1 2 000  r[2]=r[1]
5 ResultRow  2 1 000  output=r[2]
6 Halt   0 0 000
7 Transaction0 0 3 0  01  usesStmtJournal=0
8 TableLock  0 2 0 data   00  iDb=0 root=2
write=0
9 Goto   0 1 000
sqlite> explain select count(Tea) from data;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 12000  Start at 12
1 Null   0 1 200  r[1..2]=NULL
2 OpenRead   0 2 0 1  00  root=2 iDb=0; data
3 Rewind 0 7 000
4   Column 0 0 300  r[3]=data.tea
5   AggStep0   0 3 1 count(1)   01  accum=r[1]
step(r[3])
6 Next   0 4 001
7 Close  0 0 000
8 AggFinal   1 1 0 count(1)   00  accum=r[1] N=1
9 Copy   1 4 000  r[4]=r[1]
10ResultRow  4 1 000  output=r[4]
11Halt   0 0 000
12Transaction0 0 3 0  01  usesStmtJournal=0
13TableLock  0 2 0 data   00  iDb=0 root=2
write=0
14Goto   0 1 000
sqlite>

​
​NOT 

Re: [sqlite] Is there a performance difference between COUNT(*) and COUNT(name)

[R Smith]

On 2017/09/05 10:21 PM, Cecil Westerhof wrote:

I want to know the number of teas I have in stock. For this I use:
SELECT COUNT(Tea)
FROM   teaInStock

Tea cannot be NULL, so this is the same as:
SELECT COUNT(*)
FROM   teaInStock

​But I find the first more clear.
I almost always see the second variant. Is this because it is more
efficient, or are people just ‘lazy’?​



Nothing to do with laziness - depends on what is wanted:
COUNT(*) counts the rows in the DB that matches the filter (WHERE clause).
COUNT(Tea) counts the Tea column only and will return only the number of 
values that are not NULL.


A short script to demonstrate the differences:

  -- SQLite version 3.17.0  [ Release: 2017-02-13 ]  on SQLitespeed 
version 2.0.2.4.
  -- 



CREATE TABLE t(ID INT, Tea TEXT);

INSERT INTO t(ID, Tea) VALUES
(1, 'Ceylon'),
(2, Null),
(3, 'Earl Grey'),
(4, NULL),
(5, 'Jasmine')
;

SELECT * FROM t;


  --  ID  | Tea
  --  | -
  --   1  | Ceylon
  --   2  | NULL
  --   3  | Earl Grey
  --   4  | NULL
  --   5  | Jasmine

SELECT COUNT(*) FROM t;


  --   COUNT(*)
  -- 
  --   5

SELECT COUNT(Tea) FROM t;


  --  COUNT(Tea)
  -- 
  --   3

SELECT COUNT(*) FROM t WHERE Tea IS NULL;


  --   COUNT(*)
  -- 
  --   2

DROP TABLE t;

  -- 





___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users