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

[Ernesto Baschny [cron IT]]

ries van Twisk wrote: on 18.07.2008 16:35:
> 
> On Jul 18, 2008, at 9:14 AM, Martin Kutschker wrote:
> 
>> ries van Twisk schrieb:
>>>
>>> Also keep in mind that in some databases counting is much more expensive
>>> then counting in MySQL with MyISAM tables.
>>
>> But I don't beleive that counting server side is more expensive than
>> client side for any DB.
> 
> You are absolutely right in that! Best is to count it server side.
> 
>>
>>
>> But of course it doesn't make sense to do a COUNT(*) to decide if you
>> should do a full select with *exactly* the same WHERE statement. I've
>> seen that too. In this case the DB has to do the same work twice.
> 
> Fully agreed!!!
> 
> 
> I just wanted to explain that count(*) (cardinality counting) is different
> from count(field) (expression counting).
> 
> Also that counting in general is fast on MyISAM (but in-accurate under 
> some conditions)
> and can be much slower for InnoDB or other DB engines.
> 
> But you are right Masi, if you need to do a count on a set of records, 
> then count(..) is your friend
> and not to retrieve a data set and count on the client side
> 

I don't think that mysql_num_rows will be processed "client side". The 
data sets won't even be retrieved if you just do a select followed by 
mysql_num_rows. It is probably more expensive than count(*), but I 
cannot imagine that it is *that* much faster.

I recall that mysql has a shortcut in the special case "COUNT(*) FROM 
<table>" (without a "WHERE"), because it will consult the MYD headers 
for the amount of rows and not even load indexes to do the dirty work. 
So this is of course the easiest to do.

But as in TYPO3 almost on all queries you will have to include 
enableFields, I figure that MySQL will have to touch indexes anyway so 
it shouldn't matter that much.

Cheers,
Ernesto