On Tue, 5 Sep 2017 23:31:32 +0200 Cecil Westerhof <cldwester...@gmail.com> wrote:
> 2017-09-05 23:11 GMT+02:00 Simon Slavin <slav...@bigfraud.org>: > > > > > > > On 5 Sep 2017, at 9:21pm, 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’? > > > > 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 <johnfo...@asm32.info> _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
