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

[Dominique Devienne]

On Wed, Sep 6, 2017 at 7:56 AM, Hick Gunter  wrote:

> Count() needs to extract the  field from the record, tallying
> only those that are NOT NULL.
>

Technically it would not need to "extract" the  field, only lookup
the row header and
see whether that field/column is NULL or not (since NULL is a special
SERIAL TYPE of value 0 [1]).

asql> explain select count(a) from t;
> addr  opcode p1p2p3p4 p5  comment
>   -        -  --  -
> ...
> 4 OpenRead   0 2 1 1  00  t
> 5 Rewind 0 9 000  NULL
> 6 Column 0 0 300  t.a
> 7 AggStep0 3 1 count(1)   01  NULL
> ...
>

But as you showed, in practice it may still do, because of the Column VDBE
op-code.
Unless one imagines a specialized ColumnNullness of ColumnType new op-code
that
wouldn't need to extract the actual value/content, as a future
optimization. --DD

[1] https://www.sqlite.org/fileformat.html#record_format
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Hick Gunter]

Count() needs to extract the  field from the record, tallying only 
those that are NOT NULL.

Count(*) returns the total number of records in the table, with no need to 
extract a specific field.

When looking into efficiency, try using the .explain/explain feature.

asql> explain select count(a) from t;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
...
4 OpenRead   0 2 1 1  00  t
5 Rewind 0 9 000  NULL
6 Column 0 0 300  t.a
7 AggStep0 3 1 count(1)   01  NULL
8 Next   0 6 001  NULL
9 Close  0 0 000  NULL
10AggFinal   1 1 0 count(1)   00  NULL
11Copy   1 4 000  NULL
12ResultRow  4 1 000  NULL
...
asql> explain select count(*) from t;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
...
2 OpenRead   1 2 100  NULL
3 Count  1 1 000  NULL
4 Close  1 0 000  NULL
5 Copy   1 2 000  NULL
6 ResultRow  2 1 000  NULL
...

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Cecil Westerhof
Gesendet: Dienstag, 05. September 2017 22:22
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: [EXTERNAL] [sqlite] Is there a performance difference between COUNT(*) 
and COUNT(name)

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’?​

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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