optimize this

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

optimize this

Post 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>
<? } ?>
nyy2000
Forum Newbie
Posts: 15
Joined: Tue Jul 12, 2005 12:40 am

Post 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");
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

I think the problem is inside the loop.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

You could consider INNER JOIN on the tables... This way you end up with 1 query instead of multiple queries...
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Here is an example..

Code: Select all

SELECT *
FROM forumtopicmain AS ftm
INNER JOIN forumentries AS fe ON ftm.id=fe.topicid
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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)
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Once again, can't agree more ;) (And i've experienced that MySQL supports USING)
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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.
Post Reply