Can my inefficient queries be helped?

schwim

Hi there folks!

I've got a block that gets the latest posts from the database for a phpBB3 install. During page loads, it's always a little slow to populate. using microtime, I've managed to find the slow spot and it seems to be the main query that's causing issue. Here's the block:

// HTML Start.
echo ("<div align='center'>
<table width='100%' border='0' cellpadding='0' cellspacing='0'>
	<tr>
		<td width='24'><img src='/template/images/box_tl.png' width='24' height='39' alt='' title='' /></td>
		<td style=\"background-image: url(/template/images/box_t.png)\"><div class='menu_header'>Latest Posts</div></td>
		<td width='24'><img src='/template/images/box_tr.png' width='24' height='39' alt='' title='' /></td>
	</tr>
	<tr>
		<td style=\"background-image: url(/template/images/box_l.png)\"> </td>
		<td align='left'>
		<table cellpadding='0' cellSpacing='0' width='100%'>
			<tr>
				<td colspan='3' align='left'>
");

// No handy links for anon users.
if($user->data['user_id'] != ANONYMOUS){
	echo("

			<b>|-
			<a href='/forum/search.php?search_id=unreadposts' alt='View unread posts' title='View unread posts'>View unread posts</a>
			-|-
			<a href='".$mark_forums_read."' alt='Mark forums read' title='Mark forums read'>Mark forums read</a>
			-|</b>
");
}else{
	echo("
				<b>|-
				View unread posts
				-|-
				Mark forums read
				-|</b>
	");
}

echo("
				<hr />
				</td>
				<td colspan='3' align='right'>
				<b>Number of topics to view: |- ");
				if($topicnumber==20){
					echo"20";
				}else{
					echo"<a href='index.php?topicnumber=20'>20</a>";
				}
				echo" -|- ";
				if($topicnumber==30){
					echo"30";
				}else{
					echo"<a href='index.php?topicnumber=30'>30</a>";
				}
				echo" -|- ";
				if($topicnumber==40){
					echo"40";
				}else{
					echo"<a href='index.php?topicnumber=40'>40</a>";
				}
				echo(" -|</b>
				<hr />
				</td>
			</tr>
			<tr>
				<td>&nbsp;</td>
				<td>&nbsp;</td>
				<td align='left'>&nbsp;&nbsp;<b><font color='blue'>TOPICS</font></b></td>
				<td align='center'><b><font color='blue'>REPLIES</font></b></td>
				<td align='center'><b><font color='blue'>VIEWS</font></b></td>
				<td align='left'>&nbsp;&nbsp;<b><font color='blue'>LAST POST</font></b></td>
			</tr>");

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

// Clean up the SQL variables.
$forum_id = $row['forum_id'];
$topic_id = $row['topic_id'];
$post_id = $row['post_id'];
$topic_title = $row['topic_title'];
$topic_views = number_format($row['topic_views']);
$topic_replies = number_format($row['topic_replies']);
$topic_last_post_time = $row['topic_last_post_time'];
$topic_starter = $row['topic_first_poster_name'];
$topic_starter_id = $row['topic_poster'];
$topic_starter_time = $user->format_date($row['topic_time'], false, ($view == 'print') ? true : false);
$last_post_time = $user->format_date($row['post_time'], false, ($view == 'print') ? true : false);

// Need to get the latest posts identifier for registered users.
if ($user->data['user_id'] != ANONYMOUS){
	// First, let's check to see if the last forum clear has been triggered since post was made.
	if($user_forum_mark >= $topic_last_post_time){
		// Full clear has occured since post. Topic read.
		$topic_icon='/forum/styles/prosilver/imageset/topic_read.gif';
		$topic_alt='This topic has no unread posts.';
	}else{
		// Check for category mark.
		$sql5="SELECT mark_time FROM phpbb_forums_track WHERE user_id=$user_id AND forum_id=$forum_id LIMIT 1";
		$result5=mysql_query($sql5) or die (mysql_error());
		$numr5=mysql_num_rows($result5);
		if($numr5 != '0'){
			while($row5=mysql_fetch_array($result5)){
				$user_category_time=$row5['mark_time'];
			}
		}else{
			$user_category_time='0';
		}
		if($user_category_time >= $topic_last_post_time){
			// User timestamp is greater. This topic is old news.
			$topic_icon='/forum/styles/prosilver/imageset/topic_read.gif';
			$topic_alt='This topic has no unread posts.';
		}else{
			$sql2="SELECT mark_time FROM phpbb_topics_track WHERE user_id=$user_id AND topic_id=$topic_id LIMIT 1";
			$result2=mysql_query($sql2) or die (mysql_error());
			$numr2=mysql_num_rows($result2);
			//If there's a record, they have viewed the topic before.  set the proper icon.
			if($numr2!=0){
				while($row2=mysql_fetch_array($result2)){
					$user_time=$row2['mark_time'];
				}
				// Compare the times.  If the topic time is greater, it's new.
				if($user_time >= $topic_last_post_time){
					// User timestamp is greater. This topic is old news.
					$topic_icon='/forum/styles/prosilver/imageset/topic_read.gif';
					$topic_alt='This topic has no unread posts.';
				}else{
					// User timestamp is older than topic update.  Unread.
					$topic_icon='/forum/styles/prosilver/imageset/topic_unread.gif';
					$topic_alt='This topic has unread posts.';
					$show_unread = '1';
				}

			}else{
				// No record in topics_track for this user.  Definitely unread.
				$topic_icon='/forum/styles/prosilver/imageset/topic_unread.gif';
				$topic_alt='This topic has unread posts.';
				$show_unread = '1';
			}
		}
	}
}else{
	// Anonymous user, so all show as unread.
	$topic_icon='/forum/styles/prosilver/imageset/topic_unread.gif';
	$topic_alt='Login to determine unread topics.';
}

// Same old crap for alternating bg colors.
if($i%2 == 0)
{
	$td_bg ="ffffff";
	$i++;
}else{
	$td_bg ="ececec";
	$i++;
} 

echo("
		<tr>
			<td width='27' align='center' bgcolor='#".$td_bg."'>
			<img src='".$topic_icon."' alt='".$topic_alt."' title='".$topic_alt."' />
			</td>
			<td width='10' bgcolor='#".$td_bg."'>&nbsp;</td>
			<td align='left' bgcolor='#".$td_bg."'>
");

// If It's flagged, give them a handy linked img for latest post link.
if(ISSET($show_unread)){
	echo("
		<a href='$urlPath/viewtopic.php?f=".$forum_id."&t=".$topic_id."&view=unread#unread'><img src='/forum/styles/prosilver/imageset/icon_topic_newest.gif' alt='".$topic_alt."' title='".$topic_alt."' /></a> |
	");
}

echo("
			<b><a href='$urlPath/viewtopic.php?f=".$forum_id."&t=".$topic_id."'>".$topic_title."</a></b>
			<br />
			by <a href='$urlPath/memberlist.php?mode=viewprofile&u=".$topic_starter_id."'>".$topic_starter."</a> » ".$topic_starter_time."
			<!--<br />
			Topic last post time is: ".$topic_last_post_time." and user_lastmark is: ".$user_forum_mark.".-->
");



echo("
			</td>
			<td width='75' align='center' bgcolor='#".$td_bg."'>
			".$topic_replies."
			</td>
			<td width='75' align='center' bgcolor='#".$td_bg."'>
			".$topic_views."
			</td>
			<td align='left' width='175' bgcolor='#".$td_bg."'>
			by <a href='$urlPath/memberlist.php?mode=viewprofile&u=$row[user_id]'>".$row["username"]."</a>
			<a href='$urlPath/viewtopic.php?f=".$forum_id."&t=".$topic_id."&p=".$post_id."#p".$post_id."'><img src='/forum/styles/prosilver/imageset/icon_topic_latest.gif' alt='View the latest post' title='View the latest post' /></a><br />
			".$last_post_time."
			</td>
	</tr>
	<tr>
		<td colspan='6' bgcolor='#".$td_bg."'><font size='tiny'>&nbsp;</font></td>
	</tr>");
// We need to clear these variables for next loop so we don't get any false positives.
UNSET($user_time);
UNSET($show_unread);
}

echo "</table>
		</td>
		<td style=\"background-image: url(/template/images/box_r.png)\"> </td>
	</tr>
	<tr>
		<td width='24'><img src='/template/images/box_bl.png' width='24' height='24' alt='' title='' /></td>
		<td style=\"background-image: url(/template/images/box_b.png)\"> </td>
		<td width='24'><img src='/template/images/box_br.png' width='24' height='24' alt='' title='' /></td>
	</tr>
</table>
</div>";

And this is the query that seems to have the slowest timing:

// Master query for topic info.  Even I don't know what the hell is going on down there.
$query = "SELECT t.topic_id, t.topic_title, t.topic_views, t.topic_replies, t.topic_poster, t.topic_time, t.topic_first_poster_name, t.topic_last_post_id, t.topic_last_post_time, t.forum_id, p.post_id, p.poster_id, p.post_time, u.user_id, u.username
FROM $table_topics t, $table_forums f, $table_posts p, $table_users u
WHERE t.topic_id = p.topic_id AND
f.forum_id = t.forum_id AND
t.topic_status <> 2 AND
p.post_id = t.topic_last_post_id AND
p.poster_id = u.user_id
ORDER BY p.post_id DESC LIMIT $topicnumber";
$result = mysql_query($query) or die("Query failed");
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

This portion was borrowed from a block I found online. I'm wondering if there's something inherently inefficient with the query itself that you might be able to point out to me? I understand that many things impact the performance of the query but this seems to consistently have the worst impact on page load times.

Any thoughts would be welcome.

NogDog

First thing is to make sure that every table column that gets used in the WHERE or ORDER BY clause has an index assigned to it, regardless of whether it's a non-unique or unique index. (Any PRIMARY KEY columns are already unique indexes.)

schwim

Thanks very much for your help, nog!

It seems that all of the db tables have a primary index already, that are bound to the unique ID:

CREATE TABLE IF NOT EXISTS phpbb_topics (
topic_id mediumint(8) unsigned NOT NULL auto_increment,
forum_id mediumint(8) unsigned NOT NULL default '0',
icon_id mediumint(8) unsigned NOT NULL default '0',
topic_attachment tinyint(1) unsigned NOT NULL default '0',
topic_approved tinyint(1) unsigned NOT NULL default '1',
topic_reported tinyint(1) unsigned NOT NULL default '0',
topic_title varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL default '',
topic_poster mediumint(8) unsigned NOT NULL default '0',
topic_time int(11) unsigned NOT NULL default '0',
topic_time_limit int(11) unsigned NOT NULL default '0',
topic_views mediumint(8) unsigned NOT NULL default '0',
topic_replies mediumint(8) unsigned NOT NULL default '0',
topic_replies_real mediumint(8) unsigned NOT NULL default '0',
topic_status tinyint(3) NOT NULL default '0',
topic_type tinyint(3) NOT NULL default '0',
topic_first_post_id mediumint(8) unsigned NOT NULL default '0',
topic_first_poster_name varchar(255) collate utf8_bin NOT NULL default '',
topic_first_poster_colour varchar(6) collate utf8_bin NOT NULL default '',
topic_last_post_id mediumint(8) unsigned NOT NULL default '0',
topic_last_poster_id mediumint(8) unsigned NOT NULL default '0',
topic_last_poster_name varchar(255) collate utf8_bin NOT NULL default '',
topic_last_poster_colour varchar(6) collate utf8_bin NOT NULL default '',
topic_last_post_subject varchar(255) collate utf8_bin NOT NULL default '',
topic_last_post_time int(11) unsigned NOT NULL default '0',
topic_last_view_time int(11) unsigned NOT NULL default '0',
topic_moved_id mediumint(8) unsigned NOT NULL default '0',
topic_bumped tinyint(1) unsigned NOT NULL default '0',
topic_bumper mediumint(8) unsigned NOT NULL default '0',
poll_title varchar(255) collate utf8_bin NOT NULL default '',
poll_start int(11) unsigned NOT NULL default '0',
poll_length int(11) unsigned NOT NULL default '0',
poll_max_options tinyint(4) NOT NULL default '1',
poll_last_vote int(11) unsigned NOT NULL default '0',
poll_vote_change tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (topic_id),
KEY forum_id (forum_id),
KEY forum_id_type (forum_id,topic_type),
KEY last_post_time (topic_last_post_time),
KEY topic_approved (topic_approved),
KEY forum_appr_last (forum_id,topic_approved,topic_last_post_id),
KEY fid_time_moved (forum_id,topic_last_post_time,topic_moved_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=17545 ;

schwim

CREATE TABLE IF NOT EXISTS phpbb_posts (
post_id mediumint(8) unsigned NOT NULL auto_increment,
topic_id mediumint(8) unsigned NOT NULL default '0',
forum_id mediumint(8) unsigned NOT NULL default '0',
poster_id mediumint(8) unsigned NOT NULL default '0',
icon_id mediumint(8) unsigned NOT NULL default '0',
poster_ip varchar(40) collate utf8_bin NOT NULL default '',
post_time int(11) unsigned NOT NULL default '0',
post_approved tinyint(1) unsigned NOT NULL default '1',
post_reported tinyint(1) unsigned NOT NULL default '0',
enable_bbcode tinyint(1) unsigned NOT NULL default '1',
enable_smilies tinyint(1) unsigned NOT NULL default '1',
enable_magic_url tinyint(1) unsigned NOT NULL default '1',
enable_sig tinyint(1) unsigned NOT NULL default '1',
post_username varchar(255) collate utf8_bin NOT NULL default '',
post_subject varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL default '',
post_text mediumtext character set utf8 collate utf8_unicode_ci NOT NULL,
post_checksum varchar(32) collate utf8_bin NOT NULL default '',
post_attachment tinyint(1) unsigned NOT NULL default '0',
bbcode_bitfield varchar(255) collate utf8_bin NOT NULL default '',
bbcode_uid varchar(8) collate utf8_bin NOT NULL default '',
post_postcount tinyint(1) unsigned NOT NULL default '1',
post_edit_time int(11) unsigned NOT NULL default '0',
post_edit_reason varchar(255) collate utf8_bin NOT NULL default '',
post_edit_user mediumint(8) unsigned NOT NULL default '0',
post_edit_count smallint(4) unsigned NOT NULL default '0',
post_edit_locked tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (post_id),
KEY forum_id (forum_id),
KEY topic_id (topic_id),
KEY poster_ip (poster_ip),
KEY poster_id (poster_id),
KEY post_approved (post_approved),
KEY post_username (post_username),
KEY tid_post_time (topic_id,post_time),
FULLTEXT KEY post_subject (post_subject),
FULLTEXT KEY post_text (post_text),
FULLTEXT KEY post_content (post_subject,post_text)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=142980 ;

CREATE TABLE IF NOT EXISTS phpbb_users (
user_id mediumint(8) unsigned NOT NULL auto_increment,
user_type tinyint(2) NOT NULL default '0',
group_id mediumint(8) unsigned NOT NULL default '3',
user_permissions mediumtext collate utf8_bin NOT NULL,
user_perm_from mediumint(8) unsigned NOT NULL default '0',
user_ip varchar(40) collate utf8_bin NOT NULL default '',
user_regdate int(11) unsigned NOT NULL default '0',
username varchar(255) collate utf8_bin NOT NULL default '',
username_clean varchar(255) collate utf8_bin NOT NULL default '',
user_password varchar(40) collate utf8_bin NOT NULL default '',
user_passchg int(11) unsigned NOT NULL default '0',
user_pass_convert tinyint(1) unsigned NOT NULL default '0',
user_email varchar(100) collate utf8_bin NOT NULL default '',
user_email_hash bigint(20) NOT NULL default '0',
user_birthday varchar(10) collate utf8_bin NOT NULL default '',
user_lastvisit int(11) unsigned NOT NULL default '0',
user_lastmark int(11) unsigned NOT NULL default '0',
user_lastpost_time int(11) unsigned NOT NULL default '0',
user_lastpage varchar(200) collate utf8_bin NOT NULL default '',
user_last_confirm_key varchar(10) collate utf8_bin NOT NULL default '',
user_last_search int(11) unsigned NOT NULL default '0',
user_warnings tinyint(4) NOT NULL default '0',
user_last_warning int(11) unsigned NOT NULL default '0',
user_login_attempts tinyint(4) NOT NULL default '0',
user_inactive_reason tinyint(2) NOT NULL default '0',
user_inactive_time int(11) unsigned NOT NULL default '0',
user_posts mediumint(8) unsigned NOT NULL default '0',
user_lang varchar(30) collate utf8_bin NOT NULL default '',
user_timezone decimal(5,2) NOT NULL default '0.00',
user_dst tinyint(1) unsigned NOT NULL default '0',
user_dateformat varchar(30) collate utf8_bin NOT NULL default 'd M Y H:i',
user_style mediumint(8) unsigned NOT NULL default '0',
user_rank mediumint(8) unsigned NOT NULL default '0',
user_colour varchar(6) collate utf8_bin NOT NULL default '',
user_new_privmsg int(4) NOT NULL default '0',
user_unread_privmsg int(4) NOT NULL default '0',
user_last_privmsg int(11) unsigned NOT NULL default '0',
user_message_rules tinyint(1) unsigned NOT NULL default '0',
user_full_folder int(11) NOT NULL default '-3',
user_emailtime int(11) unsigned NOT NULL default '0',
user_topic_show_days smallint(4) unsigned NOT NULL default '0',
user_topic_sortby_type varchar(1) collate utf8_bin NOT NULL default 't',
user_topic_sortby_dir varchar(1) collate utf8_bin NOT NULL default 'd',
user_post_show_days smallint(4) unsigned NOT NULL default '0',
user_post_sortby_type varchar(1) collate utf8_bin NOT NULL default 't',
user_post_sortby_dir varchar(1) collate utf8_bin NOT NULL default 'a',
user_notify tinyint(1) unsigned NOT NULL default '0',
user_notify_pm tinyint(1) unsigned NOT NULL default '1',
user_notify_type tinyint(4) NOT NULL default '0',
user_allow_pm tinyint(1) unsigned NOT NULL default '1',
user_allow_viewonline tinyint(1) unsigned NOT NULL default '1',
user_allow_viewemail tinyint(1) unsigned NOT NULL default '1',
user_allow_massemail tinyint(1) unsigned NOT NULL default '1',
user_options int(11) unsigned NOT NULL default '230271',
user_avatar varchar(255) collate utf8_bin NOT NULL default '',
user_avatar_type tinyint(2) NOT NULL default '0',
user_avatar_width smallint(4) unsigned NOT NULL default '0',
user_avatar_height smallint(4) unsigned NOT NULL default '0',
user_sig mediumtext collate utf8_bin NOT NULL,
user_sig_bbcode_uid varchar(8) collate utf8_bin NOT NULL default '',
user_sig_bbcode_bitfield varchar(255) collate utf8_bin NOT NULL default '',
user_from varchar(100) collate utf8_bin NOT NULL default '',
user_icq varchar(15) collate utf8_bin NOT NULL default '',
user_aim varchar(255) collate utf8_bin NOT NULL default '',
user_yim varchar(255) collate utf8_bin NOT NULL default '',
user_msnm varchar(255) collate utf8_bin NOT NULL default '',
user_jabber varchar(255) collate utf8_bin NOT NULL default '',
user_website varchar(200) collate utf8_bin NOT NULL default '',
user_occ text collate utf8_bin NOT NULL,
user_interests text collate utf8_bin NOT NULL,
user_actkey varchar(32) collate utf8_bin NOT NULL default '',
user_newpasswd varchar(40) collate utf8_bin NOT NULL default '',
user_form_salt varchar(32) collate utf8_bin NOT NULL default '',
user_new tinyint(1) unsigned NOT NULL default '1',
user_reminded tinyint(4) NOT NULL default '0',
user_reminded_time int(11) unsigned NOT NULL default '0',
user_flagged tinyint(1) unsigned NOT NULL default '0',
user_flag_new tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (user_id),
UNIQUE KEY username_clean (username_clean),
KEY user_birthday (user_birthday),
KEY user_email_hash (user_email_hash),
KEY user_type (user_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=12243 ;

Is there something else I can do, perhaps to the query/code itself that will enhance the performance? I'm pretty sure the issue is related to the latest post code because the system has no problem showing the posts in a quick manner when inside the forum. Only the latest post block as the pause before it spits out the while loop.

NogDog

Looks like these two could use and index:

phpbb_topics.topic_status
phpbb_topics.topic_last_post_id

(topic_last_post_id is part of a multi-field index, but since not both of the two preceding fields in that key are used, it would probably help to also add a separate key just on this field)

schwim

Two things before I go and mess anything up:

1) I'm handling this at the db end(phpMyAdmin) and not at the query, correct?

2) Will adding indexes to these by any chance mess up the operation of how the forum uses these tables? I would rather the block be impacted negatively rather than the forum system.

NogDog

You should be able to do it through phpMyAdmin (I've not used that in awhile, so don't know the specific instructions -- but there should be an "Add Index" button somewhere when viewing the table structure.)

Adding an index should not hurt any functionality. It may add a little bit more processing time to inserts/updates on the affected tables, but since the performance gain on searches is often much more, plus the fact that you probably do a lot more searches than inserts/updates, it's almost always worth it. Just don't put indexes on every field if there is no reason for one.

Bonesnap

NogDog;11030213 wrote:
You should be able to do it through phpMyAdmin (I've not used that in awhile, so don't know the specific instructions -- but there should be an "Add Index" button somewhere when viewing the table structure.)

I can confirm this as I am looking at phpMyAdmin right now.

Weedpacket

For this query, an index on phpbb_topics.topic_status won't be much help, since the search is for those topics that have a status that is not 2; such an index would help find the records where the status is 2, but the table would have to be scanned to retrieve all the others.

Certainly the *_id fields use to join the tables are prime candidates for indexing. Keep in mind that the DBMS can only use one index on each table, but that an index can be on multiple fields.

dalecosp

Bonesnap;11030323 wrote:
I can confirm this as I am looking at phpMyAdmin right now.

I don't think you can do multiple indexes, though, although I'd love to be wrong about that.

bradgrafelman

dalecosp;11030335 wrote:
I don't think you can do multiple indexes, though, although I'd love to be wrong about that.

That's certainly not at all true for at least MySQL (and probably just about any other SQL RDBMS out there).