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

[Jigal van Hemert]

Dmitry Dulepov [typo3] wrote:
> Steffen Kamper wrote:
>> i tested this and it makes no difference
>> COUNT(*) or COUNT(field) is internally the same
> 
> There is a difference if you use different storage engines. MyISAM works best with COUNT(*) because it stores counter separately. But COUNT(uid) will not use this counter but count all uids using index. At least it worked this way in MySQL 4.
> 
MyISAM can only use the internal counter if there is no WHERE, etc. 
InnoDB has no internal record counter and is often much slower with 
COUNT(*).
Because you often use hidden=0 and/or deleted=0 and/or disabled=0 you 
have to rely on the table designer having made the right indexes :-)

The query cache can help if the table data doesn't change very often. 
Otherwise you might want to cache the result of the count. Ask yourself 
if it is really necessary to see accurate, real time data.

-- 
Jigal van Hemert.