Page 1 of 1

Overkill or Optimized?

Posted: Mon Oct 16, 2006 4:29 pm
by seodevhead
I am building a php/mysql application that can best be described as a "forum" much like the forums here on DevNetwork. And one of the things I am unsure of is how to go about "optimizing" a specific section of my code.

Much like this specific page you are on right now (listing all the thread posts alongside the individual "user"-specific information to the left of the posts, I am outputting a result set from the database consisting of "posts" by users of my application.

I simply have a mySQL query that grabs all the posts by user for a certain thread like so as a quick example:

Code: Select all

$query = "SELECT * FROM users, posts";
And then to output all the posts I simply do this (example):

Code: Select all

while ($rows = mysql_fetch_assoc($result))
{
      echo $rows['username'] . '<br />' . $rows['postcount'] . '<br />' . $rows['message'];
}
Obviously this is a simplified version, but is very good resemblance of the structure of my code. Now here is what I am having difficulty with....

I have a table called 'usergroup' that allows me to track which usergroup (whether it be moderator, advertiser, best avatar, etc). And from this 'usergroup' table there are many other tables normalized with this intent. But to keep things simple, most users do not belong to any usergroup and thus no record for them exists in the usergroup table. Only those users with a usergroup status have a record(s) in the usergroup table. Thus I am having difficulty figuring out how to determine if a user belongs to any usergroup(s) within the $query function above.... because a 'post' record might not be linked to any record in the usergroup table. And it keeps returning 0 results if the user who made a post does not belong to a usergroup (have a record in usergroup table).

So to fix this... I simply added another query inside my WHILE loop code listed above.

Code: Select all

while ($rows = mysql_fetch_assoc($result))
{
      echo $rows['username'] . '<br />' . $rows['postcount'] . '<br />' . $rows['message'];
 
      $query2 = "SELECT * FROM users AS u, usergroup AS ug WHERE u.user_id=ug.user_id";
      $result2 = mysql_query($query2);

      if (mysql_num_rows($result2) >= 1)
      {
            echo 'HAS A USERGROUP RECORD!';
      }
}
Thus, if there are 20 posts from various users, the inner query I have that checks to see if the users have any records in the usergroup table, is run 20 times. Is this bad? How many queries (mind you pretty simple queries) can you have in a php document before you begin to tell yourself you are unoptimized. I don't know how to control this from the one $query above.

Does this current setup I have sound acceptable to you? Thanks for your help and advice. Take care.

Posted: Mon Oct 16, 2006 4:40 pm
by feyd
A selection query in a loop I typically call a code smell. This is absolutely true when the query is not affected by variables between iterations.

Posted: Mon Oct 16, 2006 4:47 pm
by seodevhead
feyd wrote:A selection query in a loop I typically call a code smell. This is absolutely true when the query is not affected by variables between iterations.
Hey Feyd, thanks for the response, but I am having difficulty understanding what you mean by this? What is a code smell? I'm a bit green and would really appreciate some clarification on this. Thanks!

Posted: Mon Oct 16, 2006 5:55 pm
by feyd
http://c2.com/xp/CodeSmell.html defines code smell pretty well.

Posted: Mon Oct 16, 2006 7:14 pm
by Cameri
oh well...

Code: Select all

SELECT `u`.*, `ug`.*, `p`.* FROM `users` AS `u` LEFT JOIN `usergroup` AS `ug` ON (`u`.`usergroupid`=`ug`.`usergroupid`) LEFT JOIN `posts` AS `p` ON (`u`.`userid`=`p`.`userid`);
What that will do is display all the user fields, user group fields that the user has (related through `usergroupid` in this case), if he doesnt have any they appear but null, same for posts (related through `userid` in this case).

If you use:

Code: Select all

SELECT `u`.*, `ug`.*, `p`.* FROM `users` AS `u`, `usergroup`, `posts` WHERE `u`.`usergroupid`=`ug`.`usergroupid` AND `u`.`userid` = `p`.`userid;
You WON'T get the users that DON'T have any posts or that DON'T have any usergroup, because, in your sql you are implying that each entry in `users` must have a `usergroupid` field that is equal to a field with the same name in `usergroups`... you get it right?

If you don't know anything about SQL JOINS, I suggest you read about it, whether it is from a book or mysql's website, or well, from the database server you are using.

Posted: Tue Oct 17, 2006 8:42 am
by seodevhead
Cameri... man thank you so much. LEFT JOIN's completely slipped my mind. One last quick question... since each user could have many records in the 'usergroup' table (in other words, could belong to more than one usergroup)... how do I go about returning all the usergroup id's from the 'usergroup' table within that on SELECT query? It is working great as you wrote it however it is only returning the first record. Any idea how to return all the usergroup records... maybe in an array??? Thanks so much!

Posted: Tue Oct 17, 2006 8:57 am
by Jenk
further optimisation:

specify your columns, even if it's every one of them. This works for two reasons.. if your table is altered with additional columns, you'll be fetching columns that are not needed, and secondly the database server will first run a query to identify which columns the table has in the first place :)

Posted: Tue Oct 17, 2006 8:59 am
by seodevhead
Any idea how I can get all the records returned in that one query for the usergroup table? It is only returning one record for each user. If I could somehow get all the records returned for each in user in an array or something.. that would be optimal.