[TYPO3-dev] Tip: use SQL function COUNT(*)

[ries van Twisk]

On Jul 18, 2008, at 5:54 AM, Martin Kutschker wrote:

> Steffen Kamper schrieb:
>> Ingo Renner schrieb:
>>> Martin Kutschker wrote:
>>>
>>>> Anyway it's better to use this:
>>>>
>>>> $res = $GLOBALS['TYPO3_DB']->exec_SELECTquery('COUNT(*)',  
>>>> 'tx_table');
>>>
>>> $res = $GLOBALS['TYPO3_DB']->exec_SELECTquery('COUNT(uid)',  
>>> 'tx_table');
>>>
>>> ;)
>>>
>>>
>>
>> i tested this and it makes no difference
>> COUNT(*) or COUNT(field) is internally the same
>
> Internally - where? Of course it makes no difference on the client  
> side.
>
> I'm not sure if it makes a difference for the server as COUNT(*) has -
> to me - the meaning "just count the rows". So I don't think that Mysql
> will try to do anything with the whole record and is optimized to
> retrieve the row count (probably using the primary key).
>
> Masi


Actually there is a difference between count(*) and count(field_name).
However in the case of ingo it's not seen...

If for example you do a SELECT count(somefield) FROM table;
and somefield is not constrained to NOT NULL then count(somefield)
would only count all records where the field was not null.

Example:

create table test (
	field varchar(12)
);

insert into test (field) values('a');
insert into test (field) values(null);
insert into test (field) values('b');
insert into test (field) values('c');
insert into test (field) values(null);


select count(*) from test; => 5
select count(field) from test; => 3


Also this can lead to unpredicted results: SELECT sum(field)/count(*)  
FROM table;   better to use SELECT sum(field)/count(field) FROM table;

Also keep in mind that in some databases counting is much more  
expensive then counting in MySQL with MyISAM tables.

Ries