Help with query optimization

[kjtocool]

I have a query used in an AJAX call, it currently takes quite a while to come back.  Was wondering if anyone could think of a way to optimize the query (or function):

 

function sort_forum_topics($db_conn, $sort_on, $sort_direction, $forum_id, $skip) {

	$mysql = "	SELECT		e.topic_id, e.forum_id, IF( SUBSTRING( UPPER( e.topic_title ) , 1, 4 ) =  'THE ', CONCAT( SUBSTRING( e.topic_title, 5 ) ,  ', The' ) , e.topic_title ) AS topic_title, e.topic_replies, e.topic_views, 
					        	IFNULL(((a.score + b.score + c.score + d.score + f.score) / (a.num_votes + b.num_votes + c.num_votes + d.num_votes + f.num_votes)), 0) AS total_score, 
					        	IFNULL((a.num_votes + b.num_votes + c.num_votes + d.num_votes + f.num_votes), 0) AS total_votes
				FROM        phpbb_topics e LEFT JOIN
				            	(SELECT topic_id, poll_option_total * 5 AS score, poll_option_total AS num_votes FROM phpbb_poll_options WHERE poll_option_id = 1) a ON e.topic_id = a.topic_id LEFT JOIN
								(SELECT topic_id, poll_option_total * 4 AS score, poll_option_total AS num_votes FROM phpbb_poll_options WHERE poll_option_id = 2) b ON e.topic_id = b.topic_id LEFT JOIN
								(SELECT topic_id, poll_option_total * 3 AS score, poll_option_total AS num_votes FROM phpbb_poll_options WHERE poll_option_id = 3) c ON e.topic_id = c.topic_id LEFT JOIN
					            (SELECT topic_id, poll_option_total * 2 AS score, poll_option_total AS num_votes FROM phpbb_poll_options WHERE poll_option_id = 4) d ON e.topic_id = d.topic_id LEFT JOIN
				               	(SELECT topic_id, poll_option_total * 1 AS score, poll_option_total AS num_votes FROM phpbb_poll_options WHERE poll_option_id = 5) f ON e.topic_id = f.topic_id
				WHERE 		forum_id = " . $forum_id . "
				AND        	topic_type != 1
				AND         topic_type != 2
				ORDER BY	" . $sort_on . " " . $sort_direction . "
				LIMIT 		" . $skip . ", 500";

	$result = mysqli_query($db_conn, $mysql);
	$row = mysqli_fetch_assoc($result);

	$num_topics = Count_Topics($db_conn, $forum_id);
	$html = '<table border="0" cellpadding="0" cellspacing="0">';


	if ($num_topics > 500) {
		$html = $html . '<tr>
							<td colspan="5" align="right"><span class="style1">' . Get_Page_Numbers($num_topics) . '</span></td>
						</tr>';
	}


	$html = $html . '<tr>
						<td class="style2" width="350" height="35"><a href="#" class="topics_header" onclick="Header_Clicked(\'title\');">Topic Title</a></td>
						<td class="style2" width="100" align="center"><a href="#" class="topics_header" onclick="Header_Clicked(\'replies\');">Replies</a></td>
						<td class="style2" width="100" align="center"><a href="#" class="topics_header" onclick="Header_Clicked(\'views\');">Views</a></td>
						<td class="style2" width="150" align="center"><a href="#" class="topics_header" onclick="Header_Clicked(\'votes\');">Number of Votes</a></td>
						<td class="style2" width="150" align="center"><a href="#" class="topics_header" onclick="Header_Clicked(\'score\');">Average Score</a></td>
					</tr>';


	while ($row) {
		$html = $html . '<tr>
							<td class="style1" height="20"><a href="http://www.worldofkj.com/forum/viewtopic.php?f=57&t=' . $row['topic_id'] . '" alt="Topic Title">' . iconv("ISO-8859-1", "UTF-8", $row['topic_title']) . '</a></td>
							<td class="style1" align="center">' . $row['topic_replies'] . '</td>
							<td class="style1" align="center">' . $row['topic_views'] . '</td>
							<td class="style1" align="center">' . $row['total_votes'] . '</td>
							<td class="style1" align="center">' . $row['total_score'] . '</td>
						</tr>';	

		$row = mysqli_fetch_assoc($result);
	}

	$html = $html . '</table>';

	mysqli_free_result($result);
	return $html;

}

 

Any help would be greatly appreciated, as I can't seem to get it any quicker than it currently is.

 

[fenway]

Yikes... why do you have 5 left joins for the same base query?

[kjtocool]

Couldn't think of any other way to get the data how I needed it.  Any ideas?

[fenway]

Why not

 

SELECT topic_id, ( 6 - poll_option_id ) AS score, poll_option_total AS num_votes FROM phpbb_poll_options