Overkill or Optimized?
Posted: Mon Oct 16, 2006 4:29 pm
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:
And then to output all the posts I simply do this (example):
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.
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.
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";Code: Select all
while ($rows = mysql_fetch_assoc($result))
{
echo $rows['username'] . '<br />' . $rows['postcount'] . '<br />' . $rows['message'];
}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!';
}
}Does this current setup I have sound acceptable to you? Thanks for your help and advice. Take care.