Can my inefficient queries be helped?
Results 1 to 11 of 11

Thread: Can my inefficient queries be helped?

  1. #1
    Closet Nice Guy schwim's Avatar
    Join Date
    Jun 2006
    Location
    State of confusion
    Posts
    660

    Can my inefficient queries be helped?

    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:

    Code:
    // 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:

    Code:
    // 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.
    Warning: If you are taking advice from me, then you are in really bad shape. Let's both hope for your code's sake that someone comes along and pulls us out of this fix that I've gotten us into.

  2. #2
    High Energy Magic Dept. NogDog's Avatar
    Join Date
    Aug 2006
    Location
    Ankh-Morpork
    Posts
    13,816
    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.)
    Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be." ~ from Nation, by Terry Pratchett

    "But the main reason that any programmer learning any new language thinks the new language is SO much better than the old one is because hes a better programmer now!" ~ http://www.oreillynet.com/ruby/blog/...ck_to_p_1.html


    eBookworm.us

  3. #3
    Closet Nice Guy schwim's Avatar
    Join Date
    Jun 2006
    Location
    State of confusion
    Posts
    660
    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=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=17545 ;
    Warning: If you are taking advice from me, then you are in really bad shape. Let's both hope for your code's sake that someone comes along and pulls us out of this fix that I've gotten us into.

  4. #4
    Closet Nice Guy schwim's Avatar
    Join Date
    Jun 2006
    Location
    State of confusion
    Posts
    660
    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=MyISAM 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=MyISAM 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.
    Warning: If you are taking advice from me, then you are in really bad shape. Let's both hope for your code's sake that someone comes along and pulls us out of this fix that I've gotten us into.

  5. #5
    High Energy Magic Dept. NogDog's Avatar
    Join Date
    Aug 2006
    Location
    Ankh-Morpork
    Posts
    13,816
    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)
    Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be." ~ from Nation, by Terry Pratchett

    "But the main reason that any programmer learning any new language thinks the new language is SO much better than the old one is because hes a better programmer now!" ~ http://www.oreillynet.com/ruby/blog/...ck_to_p_1.html


    eBookworm.us

  6. #6
    Closet Nice Guy schwim's Avatar
    Join Date
    Jun 2006
    Location
    State of confusion
    Posts
    660
    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.
    Warning: If you are taking advice from me, then you are in really bad shape. Let's both hope for your code's sake that someone comes along and pulls us out of this fix that I've gotten us into.

  7. #7
    High Energy Magic Dept. NogDog's Avatar
    Join Date
    Aug 2006
    Location
    Ankh-Morpork
    Posts
    13,816
    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.
    Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be." ~ from Nation, by Terry Pratchett

    "But the main reason that any programmer learning any new language thinks the new language is SO much better than the old one is because hes a better programmer now!" ~ http://www.oreillynet.com/ruby/blog/...ck_to_p_1.html


    eBookworm.us

  8. #8
    Senior Member
    Join Date
    Mar 2009
    Location
    Canada
    Posts
    794
    Quote Originally Posted by NogDog View Post
    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.
    Prison of Mirrors
    Declare variables, not war.

  9. #9
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,770
    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.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  10. #10
    Settled 4 red convertible dalecosp's Avatar
    Join Date
    Jul 2002
    Location
    Accelerating Windows at 9.81 m/s....
    Posts
    7,618
    Quote Originally Posted by Bonesnap View Post
    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.
    /!!\ mysql_ is deprecated --- don't use it! Tell your hosting company you will switch if they don't upgrade! /!!!\ ereg() is deprecated --- don't use it!

    dalecosp "God doesn't play dice." --- Einstein "Perl is hardly a paragon of beautiful syntax." --- Weedpacket

    Getting Help at All --- Collected Solutions to Common Problems --- Debugging 101 --- Unanswered Posts --- OMBE: Office Machines, Business Equipment

  11. #11
    Pna lbh ernq guvf
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,348
    Quote Originally Posted by dalecosp View Post
    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).

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •