StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOptimising My MySQL Query
text
Body
copied!<p>I'm creating a forum, and needed a little feedback. I've been programming for a long time, but I'm fairly knew to MySQL, and wondered if I'm doing this correctly.</p> <p>This is the query I'm using to output thr topic index of a selected forum. I'm trying to collect all the information in a single query, as opposed to only collecting the topic info, and then collecting the post and member info once in every in every loop, totaling to 35 individual queries(<em>one for each topic</em>). Is that clear?</p> <p>Basically, it works. It's a bit slow (<em>approximately 1 second</em>) even with only 300-400 topics.</p> <p>I've used profiling, and gathered that the most time consuming actions are: Copying to tmp table: 0.538037 Sending data: 0.206218 Copying to group table: 0.137549</p> <p>Edit: The query will be run once each time someone enters the page(about 200-300 times an hour judging by site activity). I intend to retrieve about 4000-5000 rows at a time.</p> <p>My question is: Is this the best method? Is there anything I could do to optimise this query? Or Would I be better off having lots of smaller queries executed inside a loop?</p> <pre><code>$user_id: id of the user $forum_id: id of the selected forum SELECT # Topic records `topics`.*, # First Post GROUP_CONCAT(`posts`.`id`) AS `post_id`, # All post ids `posts`.`post` AS `post_first_msg`, `posts`.`date` AS `post_first_date`, `posts`.`member` AS `post_first_member`, # First member `members`.`id` AS `member_id`, `members`.`username` AS member_username, # Returns last 3 members' id SUBSTRING_INDEX( GROUP_CONCAT(`members`.`id` ORDER BY `posts`.`date` DESC SEPARATOR '\\\\'), '\\\\', 3 ) AS member_last_id, # Returns last 3 members' username SUBSTRING_INDEX( GROUP_CONCAT(`members`.`username` ORDER BY `posts`.`date` DESC SEPARATOR '\\\\'), '\\\\', 3 ) AS member_last_username, # Get last viewed topic id `member_topic_lastview`.`lastview` AS `lastview` FROM `topics` # Returns the `posts` table in ASC order JOIN ( SELECT * FROM `posts` ORDER BY `posts`.`date` ASC ) AS `posts` ON `posts`.`topic` = `topics`.`id` # Member table, only the columns needed JOIN ( SELECT `members`.`id`, `members`.`username` FROM `members` ) AS `members` ON `members`.`id` = `posts`.`member` # Forum table, containing the forum info LEFT JOIN `forum` ON `forum`.`id` = `topics`.`forum` # Table containing the latest viewed post id of each topic LEFT JOIN ( SELECT * FROM `member_topic_lastview` WHERE `member_topic_lastview`.`member` = '$user_id' ) AS `member_topic_lastview` ON `member_topic_lastview`.`topic` = `topics`.`id` WHERE `forum` > $forum_id GROUP BY `topics`.`id` ORDER BY `topics`.`sticky` DESC, MAX(`posts`.`date`) DESC LIMIT 35 </code></pre> <p><strong>Thank you very much!</strong></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload