[PHP-DB] Impact of MySQL Queries
I'm working on a forum. When
it displays a forum index, it gets all the topics and depending on the
offset, displays only ten at a time. Now that's a single query. But
problem comes when I display how many replies each post has. So far, when
it does the while loop, it does a query for each item. Here's the code:
$indexresult = mysql_query(SELECT
uid,userpost,datetime,body,head,editdatetime,edituserpost,
sticky,room,locked,lastpostdate,lastpostuser FROM orinsbox.forumposts
WHERE ROOM = '.$_GET['room'].' AND .filter($filter).
ORDER BY .sortby($sortby).)
$while_offset = 0; // This is the counter used to check what post the
while loop is on
$while_count = 1; // This is how many posts have been displayed (once
it has reached the offset)
// Print out posts
while ($row = mysql_fetch_assoc($indexresult)) // Run through all the posts
{
$while_offset++;
if ($while_offset $offset AND $while_count = 10){ // if the
post is in the range
$while_count++;
// Get num of replies
$repliesresult = mysql_query(SELECT
uid FROM orinsbox.forumposts
WHERE common = '.$row[uid].')
or die (Unable to run query, YOU SUCK: .mysql_error());
$replies = mysql_num_rows($repliesresult);
// This is setting the image to be used for the post
if($row[locked] != NULL AND $row[sticky] != NULL
){$icon = img src=\images/gold_lock.gif\ width=\18\ height=\24\ title=\Locked
and Sticky\;}
elseif($row[locked] != NULL AND $row[sticky] == NULL
){$icon = img src=\images/silver_lock.gif\ width=\18\ height=\24\
title=\Locked\;}
elseif($row[locked] == NULL AND $row[sticky] != NULL
){$icon = img src=\images/gold_nugget.gif\ width=\18\ height=\24\
title=\Sticky\;}
else {$icon = NULL;}
// $row[descrip]
if (!isset($bgcolour) OR $bgcolour == FF){$bgcolour =
EE;}else{$bgcolour = FF;} // For alternating backround colours of the cells
if (!isset($row[common]) OR $row[common] == NULL){
echo tr bgcolor=\#.$bgcolour.\ height=24;
echo td width=\18\.$icon./td
tda
href=forums.php?room=.urlencode($_GET['room']).doForums=viewtopicobject=.$row[uid].offset=.$offset..$row[head]./a/td
td.ucwords($row[userpost])./tdtd.$replies./td
tdOn .$row[lastpostdate];
if ($row[lastpostuser] != NULL){ by
.$row[lastpostuser];}
echo /td;
echo /tr;}
} else {// Don't post it
}
}
The 'uid' column is the unique number of the post. All posts have this.
Now the column called 'common' is for replies. Posts that are to appear
in the forum index don't have a 'common' set.
If it is a reply, the column 'common' is set to the uid of the post it is
a reply too.
Example: Post 1:
Subject: Hey all how do I do this UID: marforu200401F COMMON: NULL
Post 2:
Subject: Hey this is how you do it! UID: jayforu200404A COMMON:
marforu200401F
Is there an easier way to do this, maybe with a sub select statement
maybe? I only know basic mysql. I hope this is understandable...
Oh yeah, you might wonder why I use the custom offset instead of using
the LIMIT and OFFSET clauses in the main MySQL query. The problem with
this is
that when I order the posts, it would only order them by the one gotten,
not them all, which is what I wanted.
--
Marcjon
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Impact of MySQL Queries
Marcjon, I think you have asked this question before. there are replies
to it, have you checked those?
Marcjon Louwersheimer wrote:
I'm working on a forum. When
it displays a forum index, it gets all the topics and depending on the
offset, displays only ten at a time. Now that's a single query. But
problem comes when I display how many replies each post has. So far, when
it does the while loop, it does a query for each item. Here's the code:
...
while ($row = mysql_fetch_assoc($indexresult)) // Run through all the posts
{
$while_offset++;
if ($while_offset $offset AND $while_count = 10){ // if the
post is in the range
$while_count++;
this would mean you fetch the whole result (not just 10 lines) and loop
over all of it.
it would be much better to make use of the mySQL syntax:
[LIMIT [offset,] row_count | row_count OFFSET offset]
see: http://www.mysql.com/doc/en/SELECT.html
free advice: take the time to read manuals even if your not stuck on a
particular problem, you often come across solutions to problems you
haven't encountered yet! if you're serious about PHP and mySQL then
reading both manuals back2front will save you time in the long run +
open your eyes to a whole lot of stuff - if you don't understand
something, skip it and move on - something you read later will probably
help to explain it.
...
Is there an easier way to do this, maybe with a sub select statement
yes.
maybe? I only know basic mysql. I hope this is understandable...
subselects are available in mySQL 4 I believe (don't hold me to it)...
my current DB preference goes to Firebird 1.5
Oh yeah, you might wonder why I use the custom offset instead of using
the LIMIT and OFFSET clauses in the main MySQL query. The problem with
this is
that when I order the posts, it would only order them by the one gotten,
not them all, which is what I wanted.
which is the same thing, assuming you are using the same ORDER BY
clause. ok, so in one case the result set doesn't contain certain rows
but these are the rows that you wouldn't show anyway.
your original question about minimizing the number of SQL queries is
answered in the reply(s?) to one of your other posts.
subject= Re: [PHP-DB] Forum Script
date = 3/7/2004 9:23 PM (god knows how accurate that is)
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Impact of MySQL Queries
I have IIS 4.x and MySQL running on the same machine. So do queries use up bandwidth? Does it matter if I have 20 queries per page? Will that slow it down if many people started using my site? Currently only I, and some friends for testing, use it. -- Marcjon -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Impact of MySQL Queries
Think of bandwidth as the volume of data returned by the web server to the browser, not machine cycles. MySQL will use machine cycles, but there's more to it than that. For fastest response the fields you are running the query against should be indexed. What pushes you to 20 queries per page? That seems a little excessive, as each of those is a hit to the database. Are the fields indexed? Are they cascading queries, the second depending on results returned by the first, the third upon the second, and so forth? Could you handle your returned result set by getting more data and grouping it? More information is needed to answer your question properly. Regards - Miles Thompson At 11:16 AM 3/11/2004 -0800, Marcjon Louwersheimer wrote: I have IIS 4.x and MySQL running on the same machine. So do queries use up bandwidth? Does it matter if I have 20 queries per page? Will that slow it down if many people started using my site? Currently only I, and some friends for testing, use it. -- Marcjon -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Impact of MySQL Queries
If both services are running on the same machine then no, the number of queries per page will not affect bandwidth. It will however, affect you processor usage. Without having seen your code, I can only make generalizations. 20 queries is a bit excessive but it's still manageable if the server is not being hit too heavy. To answer your question directly, yes, it will start to slow down as you have more people using the system. I would suggest you consider the following. 1: Use ADODB as your abstraction layer. It has built in query caching and could dramatically improve page speed as well as lower processor usage. 2: Take a hard look at your code and make sure you actually need 20 queries on a page. Would a single query be possible and then spin through the result set? 3: Consider moving to a Linux box. (Sorry, had to be said) Without the overhead of a GUI, your processor will be more available for mysql and your web server. 4: Make sure you have the proper indexes set. If you do need 20 queries, 20 fast queries are better than 20 slow queries. :) HTH. Let me know how I may be of service, =C= * Cal Evans * http://www.eicc.com * We take care of your IT, * So you can take care of your business. * * I think inside the sphere. Marcjon Louwersheimer wrote: I have IIS 4.x and MySQL running on the same machine. So do queries use up bandwidth? Does it matter if I have 20 queries per page? Will that slow it down if many people started using my site? Currently only I, and some friends for testing, use it. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
