On Tue, Sep 5, 2017 at 3:21 PM, Cecil Westerhof <[email protected]>
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 p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 7 0 00 Start at 7
1 OpenRead 1 2 0 1 00 root=2 iDb=0
2 Count 1 1 0 00 r[1]=count()
3 Close 1 0 0 00
4 Copy 1 2 0 00 r[2]=r[1]
5 ResultRow 2 1 0 00 output=r[2]
6 Halt 0 0 0 00
7 Transaction 0 0 1 0 01 usesStmtJournal=0
8 TableLock 0 2 0 data 00 iDb=0 root=2
write=0
9 Goto 0 1 0 00
sqlite> explain select count(tea) from data;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 12 0 00 Start at 12
1 Null 0 1 2 00 r[1..2]=NULL
2 OpenRead 0 2 0 1 00 root=2 iDb=0; data
3 Rewind 0 7 0 00
4 Column 0 0 3 00 r[3]=data.tea
5 AggStep0 0 3 1 count(1) 01 accum=r[1]
step(r[3])
6 Next 0 4 0 01
7 Close 0 0 0 00
8 AggFinal 1 1 0 count(1) 00 accum=r[1] N=1
9 Copy 1 4 0 00 r[4]=r[1]
10 ResultRow 4 1 0 00 output=r[4]
11 Halt 0 0 0 00
12 Transaction 0 0 1 0 01 usesStmtJournal=0
13 TableLock 0 2 0 data 00 iDb=0 root=2
write=0
14 Goto 0 1 0 00
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 p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 7 0 00 Start at 7
1 OpenRead 1 2 0 1 00 root=2 iDb=0
2 Count 1 1 0 00 r[1]=count()
3 Close 1 0 0 00
4 Copy 1 2 0 00 r[2]=r[1]
5 ResultRow 2 1 0 00 output=r[2]
6 Halt 0 0 0 00
7 Transaction 0 0 3 0 01 usesStmtJournal=0
8 TableLock 0 2 0 data 00 iDb=0 root=2
write=0
9 Goto 0 1 0 00
sqlite> explain select count(Tea) from data;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 12 0 00 Start at 12
1 Null 0 1 2 00 r[1..2]=NULL
2 OpenRead 0 2 0 1 00 root=2 iDb=0; data
3 Rewind 0 7 0 00
4 Column 0 0 3 00 r[3]=data.tea
5 AggStep0 0 3 1 count(1) 01 accum=r[1]
step(r[3])
6 Next 0 4 0 01
7 Close 0 0 0 00
8 AggFinal 1 1 0 count(1) 00 accum=r[1] N=1
9 Copy 1 4 0 00 r[4]=r[1]
10 ResultRow 4 1 0 00 output=r[4]
11 Halt 0 0 0 00
12 Transaction 0 0 3 0 01 usesStmtJournal=0
13 TableLock 0 2 0 data 00 iDb=0 root=2
write=0
14 Goto 0 1 0 00
sqlite>
NOT NULL doesn't make a difference. The EXPLAIN shows the operations, and
the first seems to be much more efficient; fewer steps & no loop.
>
> --
> Cecil Westerhof
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
Caution! The OP is an hyperpolysyllabicsesquipedalianist and this email may
cause stress to those with hippopotomonstrosesquipedaliophobia.
Maranatha! <><
John McKown
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
