Page 1 of 1

optimize this

Posted: Mon Jul 25, 2005 12:50 am
by s.dot
Someone please explain to me why this takes an insane amount of time to load. I've sectioned off the code and this is the part that takes about 15 seconds to load.

Code: Select all

<? $sql_text = ("SELECT id, topicname, threadstatus, author FROM forumtopicmain WHERE forumid = '$forumid' AND type != 'sticky' ORDER BY lastreply DESC");
$query = mysql_query($sql_text) ." LIMIT 0, 25";

// Show the non-stickied topics (most of them)
while($array = mysql_fetch_assoc($query))
{ 
	$replies = mysql_num_rows(mysql_query("SELECT id FROM forumentries WHERE topicid = '".$array['id']."'")) - 1;
	$lastpostarray = mysql_fetch_assoc(mysql_query("SELECT time, author FROM forumentries WHERE topicid = '".$array['id']."' ORDER BY time2 DESC LIMIT 1"));

	// Show the number of pages for each individual topic
	$query2 = mysql_query("SELECT id FROM forumentries WHERE topicid = '".$array['id']."'");
	$numberofpages = (int)(((mysql_num_rows($query2) - 1)/25) + 2); ?>
	<tr>
		<td bgcolor="#E9EBFC" class="main" width="5%" align="center" style="border-left: solid 1px #000000; border-bottom: solid 1px #000000;">
		<? if($array['threadstatus'] == "open"){ echo "<img src=\"images/thread_open.gif\" alt=\"Thread Open\">"; } ELSE { echo "<img src=\"images/thread_locked.gif\" alt=\"Thread Locked\">"; } ?>
		</td>
		<td bgcolor="#E9EBFC" class="main" width="45%" align="left" style="border-left: solid 1px #000000; border-bottom: solid 1px #000000;"><a href="showthread.php?threadid=<? echo $array['id']; ?>&page=1"><? echo stripslashes($array['topicname']); ?></a>
		<? if($numberofpages > 2)
		{
			echo "<BR><font size=1>Page: </font>";
			for ($j=1;$j<$numberofpages;$j++)
			{
				echo "<a href=\"showthread.php?threadid=".$array['id']."&page=$j\">$j</a> ";
			}
		} ?>
		</td>
		<td bgcolor="#E9EBFC" class="main" width="10%" align="center" style="border-left: solid 1px #000000; border-bottom: solid 1px #000000;"><B><? echo $replies; ?></B>
		</td>
		<td bgcolor="#E9EBFC" class="main" width="15%" align="center" style="border-left: solid 1px #000000; border-bottom: solid 1px #000000;"><a href="showme.php?u=<? echo $array['author']; ?>"><? echo $array['author']; ?></a>
		</td>
		<td bgcolor="#E9EBFC" class="main" width="25%" align="left" style="border-left: solid 1px #000000; border-bottom: solid 1px #000000; border-right: solid 1px #000000;"><? echo $lastpostarray['time']; ?><BR>by <a href="showme.php?u=<? echo $lastpostarray['author']; ?>"><? echo $lastpostarray['author']; ?></a>
		</td>
	</tr>
<? } ?>

Posted: Mon Jul 25, 2005 1:34 am
by nyy2000
How about this?

Code: Select all

$sql_text = ("SELECT id, topicname, threadstatus, author FROM forumtopicmain WHERE forumid = '$forumid' AND type != 'sticky' ORDER BY lastreply DESC LIMIT 0, 25");

Posted: Mon Jul 25, 2005 1:53 am
by s.dot
I think the problem is inside the loop.

Posted: Mon Jul 25, 2005 8:09 am
by timvw
You could consider INNER JOIN on the tables... This way you end up with 1 query instead of multiple queries...

Posted: Mon Jul 25, 2005 9:48 am
by pickle
These could be 1 query, assuming you want that order for all the queries.

Code: Select all

$replies = mysql_num_rows(mysql_query("SELECT id FROM forumentries WHERE topicid = '".$array['id']."'")) - 1;
$lastpostarray = mysql_fetch_assoc(mysql_query("SELECT time, author FROM forumentries WHERE topicid = '".$array['id']."' ORDER BY time2 DESC LIMIT 1"));
$query2 = mysql_query("SELECT id FROM forumentries WHERE topicid = '".$array['id']."'");
just make your query:

Code: Select all

SELECT
  time,
  author,
  id
FROM
  forumentries
WHERE
  tipicid = '$array[id]'
ORDER BY
  time2 DESC
Then call mysql_num_rows for your row count, and just take the first row for your last post.

Posted: Mon Jul 25, 2005 10:01 am
by onion2k
scrotaye wrote:I think the problem is inside the loop.
nyy2000 is spot on. Your "limit 0,25" is not being added to the sql, so it's currently getting everything from the database.

Posted: Mon Jul 25, 2005 12:58 pm
by s.dot
timvw wrote:You could consider INNER JOIN on the tables... This way you end up with 1 query instead of multiple queries...
Could you please point me to, or show me an example of INNER JOIN, I've never used it before.

Posted: Mon Jul 25, 2005 2:17 pm
by timvw
Here is an example..

Code: Select all

SELECT *
FROM forumtopicmain AS ftm
INNER JOIN forumentries AS fe ON ftm.id=fe.topicid

Posted: Mon Jul 25, 2005 2:25 pm
by nielsene
Of course you can abbreviate "INNER JOIN" as "JOIN" -- INNER is assumed unless you have LEFT,RIGHT,FULL,CROSS, etc

This is also why I would dislike naming the primary key of each table "id".
If forumtopicsmain primary key had been topicid (so forumentries and forumtopicmain used the same name you could use any of (assuming MySQL supports this?)

Code: Select all

SELECT * FROM forumtopicmain NATURAL JOIN forumentries
SELECT * FROM forumtopicmain JOIN forumentries USING (topicid)
JOIN ... USING (column) says to do the "=" join on the matching column names. NATURAL JOIN is identical to JOIN .. USING (ALL shared columns)

Posted: Mon Jul 25, 2005 3:49 pm
by timvw
Once again, can't agree more ;) (And i've experienced that MySQL supports USING)

Posted: Mon Jul 25, 2005 3:55 pm
by nielsene
timvw wrote:Once again, can't agree more ;) (And i've experienced that MySQL supports USING)
Cool, I think MySQL didn't support it last time I checked, so I'm glad they do now.