[nuno]

Originally posted by pwr_sneak

Matt runs Sybase instead of MySQL, you can't compare this.
MySQL optimizes count(*) queries because it keeps an extra counter in its index-files when using MyISAM Tables. The Index should be read from RAM on heavy-used tables. This kind of queries as well as MAX(indexed-column) can be answered without I/O which makes it really fast.

this sum(...,1,0) trick has to read every row from the table or just every entry in its index if its condition matches from index. so this is always slower than reading three single values with three single queries.

Originally posted by FireFly
I did that Matt.

PHP Code:

  $allpm=$DB_site->query_first("SELECT COUNT(*) AS messages,
                                SUM(IF(dateline>$bbuserinfo[lastvisit] AND folderid=0,1,0)) AS newpm,
                                SUM(IF(messageread=0 AND folderid=0,1,0)) AS unreadpm
                                FROM privatemessage WHERE userid=$bbuserinfo[userid] $ignoreusers");

  $newpm['messages']=$allpm['newpm'];
  $unreadpm['messages']=$allpm['unreadpm']; 


Well I copied it from somewhere, don't really remember right now. But the problem is the query is slower for users with lots and lots of PM's (like myself), but considering most people don't I thought it would be wiser to just use that one query.

Chen already said one query is slower, however, this only occurs when you have a considerable amount of PM's stored.
So, i think your assumption is not correct.
It is faster for users with few pm's.