On Tue, Sep 5, 2017 at 3:21 PM, Cecil Westerhof <cldwester...@gmail.com> 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 > sqlite-users@mailinglists.sqlite.org > 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users