Left join on last record (simple discussion board with topics, last posts and users)

Question

I'm trying to make a simple discussion board and as a result I need topics titles with their authors and authors of topics last posts.

So far I have this

SELECT
    `t`.`id`,
    `t`.`title`,
    `t`.`date` as theme_date,
    `u`.`id` as user_id,
    `u`.`username` as user_username,
    COUNT(p.id) as count_posts,
    SUBSTRING_INDEX(GROUP_CONCAT(`p`.`id` ORDER BY `p`.`id` DESC SEPARATOR "| " ), "| ", 1) as last_post_id,
    SUBSTRING_INDEX(GROUP_CONCAT(`p`.`date` ORDER BY `p`.`id` DESC SEPARATOR "| " ), "| ", 1) as last_post_date,
    IFNULL(MAX(p.date), t.date) AS pts
FROM
    `topics` t
LEFT JOIN
    `posts` p ON `p`.`topics_id` = `t`.`id`
LEFT JOIN
    `users` u ON `u`.`id` = `t`.`user_id`
GROUP BY
    `t`.`id`
ORDER BY
    `pts` DESC
LIMIT 10

I got here 10 latest topics titles, their authors usernames and IDs, number of messages in each topic, last ID and date of post in each topic, and everything sorted by activity... basically everything. All I need is authors username of those last posts. I guess I have to make one left join with subquery but I'm kinda stuck. Can someone help me?

eggyal · Accepted Answer · 2014-07-29T07:17:07.303

Rather than form strings containing the concatenation of the entire thread's posts from which you then obtain the first substring (ordered by id), which is not only a performance drag (because it's not sargable and requires expensive string operations) but is also subject to bugs (should the separator happen to appear within the strings being concatenated or the concatenated result exceed MySQL's limits)…

What you are really looking to do instead is obtain the groupwise maximum, which can be obtained by joining the posts table with a subquery upon it. You then merely need join the users table a second time (this time against the last post) to obtain the desired username:

SELECT   topic.id
 ,       topic.title
 ,       topic.date
 ,       topic.user_id
 ,       topic_user.username
 ,       t.count_posts
 ,       lastpost.id
 ,       lastpost.date
 ,       lastpost_user.username
 ,       IFNULL(lastpost.date, topic.date) AS pts

FROM     topics topic
         LEFT JOIN users topic_user ON topic_user.id = topic.user_id
         LEFT JOIN (
           (
             SELECT   topics_id
               ,      COUNT(*)  AS count_posts
               ,      MAX(date) AS date
             FROM     posts
             GROUP BY topics_id
           ) t  JOIN posts lastpost USING (topics_id, date)
           LEFT JOIN users lastpost_user ON lastpost_user.id = lastpost.user_id
         ) ON lastpost.topics_id = topic.id

ORDER BY pts DESC

LIMIT    10

Note that I'm also identifying the lastpost by date rather than id as synthetic keys shouldn't really be used to ascertain natural properties; however, this assumes that date is actually a timestamp.

Looks nice but... Column 't.date' in field list is ambiguous — Goldie, Jul 28 '14 at 16:10
@Goldie: Oh, sorry - I have aliased the table derived from the subquery as `t`, which is the same as you were using for the `topics` table. Just change the `t` before `USING` to something else, say `z`. — eggyal, Jul 28 '14 at 16:21
Thank you very much, eggyal. I have tested this query 2 times in phpMyAdmin with 136 topics, 22770 posts and 17424 users in DB. First time query took 317.8966 sec and second time 343.3586 sec. That is waaaaaaaaay too long. Do you know what could be the problem and is there anything that can make query faster? — Goldie, Jul 29 '14 at 09:01
@Goldie: You almost certainly need to define (better) indexes, but determining what they should be depends on the cardinality of the relevant columns and is a topic in its own right. The best thing might be to post a new question detailing the table definitions (output from `SHOW CREATE TABLE ...`), the above query and its execution plan (output of `EXPLAIN SELECT ...`). — eggyal, Jul 29 '14 at 12:50
Indexing - that was the key word. After indexing query took 0.4811 sec — Goldie, Jul 29 '14 at 18:57

Left join on last record (simple discussion board with topics, last posts and users)

1 Answers1