29 queries on Forumhome !!!

[Admin]

Send me your index.php by email and I'll take a look. You must have a query inside a loop which is how you get to 40.

[Jawelin]

It's comin'... Actually I don't think there's any recursive or iterative call... but I'm sending it to you.

LMK

Thanks a lot.
Bye

[Erwin]

Thanks for the $templatesused hint - saved me quite a few queries on my index, forumdisplay and showthread!

[MattR]

You can tie the 3 PM queries into one if you're good.

[Admin]

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.

[Erwin]

You're right - I reduced my index.php queries from 52 to 33 using all the stuff I learnt from here, WITHOUT reducing the number of hacks I have.

[MattR]

Originally posted by FireFly
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.

I think I suggested something like that on the vB Dev forum. At least with Sybase it is significantly faster than the 3 queries and with less I/O.

[FWC]

Originally posted by FireFly
Well I copied it from somewhere, don't really remember right now.

I just copied it from here. Thanks, Chen!

[Admin]

Thank Matt.

[Jawelin]

I suddendly grabbed your precious trick !


Well; I would use the same SUM(IF()) or COUNT(IF()) constructs on tables like post or thread, to reduce the query count when I check for posts since a time, per user, and so on...
(note: I built indexes on those 'time' fields)

My question is: should a COUNT(IF()) clause much heavy than three or forur different SELECTS, on such a large tables ?

Thanks