Optimising My MySQL Query

Question

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.

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(one for each topic). Is that clear?

Basically, it works. It's a bit slow (approximately 1 second) even with only 300-400 topics.

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

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.

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?

$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

Thank you very much!

Answer 1

The first thing to do is look at your explain plan and post that. The other thing that stands out is that you don't really want to do a lot of sub-queries.

Just to give a start, looking at your first join

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`

This is probably going to be much faster if you just write it like:

FROM
  `topics`

# Returns the `posts` table in ASC order
JOIN `posts` ON `posts`.`topic` = `topics`.`id`