kjtocool Posted January 20, 2009 Share Posted January 20, 2009 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. Link to comment https://forums.phpfreaks.com/topic/141677-help-with-query-optimization/ Share on other sites More sharing options...
fenway Posted January 27, 2009 Share Posted January 27, 2009 Yikes... why do you have 5 left joins for the same base query? Link to comment https://forums.phpfreaks.com/topic/141677-help-with-query-optimization/#findComment-747495 Share on other sites More sharing options...
kjtocool Posted January 31, 2009 Author Share Posted January 31, 2009 Couldn't think of any other way to get the data how I needed it. Any ideas? Link to comment https://forums.phpfreaks.com/topic/141677-help-with-query-optimization/#findComment-751093 Share on other sites More sharing options...
fenway Posted February 2, 2009 Share Posted February 2, 2009 Why not SELECT topic_id, ( 6 - poll_option_id ) AS score, poll_option_total AS num_votes FROM phpbb_poll_options Link to comment https://forums.phpfreaks.com/topic/141677-help-with-query-optimization/#findComment-752453 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.