SQL Efficiency

[jgd12345]

Hi, I have the following SQL query setup to return all the forums including no. of posts/topics and the last poster in each forum. I have the following database structure:

 

forums

- forum_id

- category_id

- forum_name

- display_order

 

topics

- topic_id

- forum_id

- subject

- last_post (date last post made)

 

posts

- post_id

- topic_id

- date_line (date posted)

 

Here's the final query i came up with (with abit of help):

 

SELECT f.forum_id
     , f.category_id
     , f.forum_name
     , f.display_order
     , COUNT(DISTINCT t.topic_id) AS num_topics 
     , COUNT(p.topic_id) AS num_posts
     , MAX(p.date_line) AS last_post
     , (SELECT post_id FROM " . posts
          WHERE topic_id = (select topic_id
           FROM topics
          WHERE forum_id = f.forum_id
            ORDER BY last_post DESC LIMIT 0, 1) ORDER BY date_line DESC LIMIT 0, 1
            ) AS last_post_id 
     , (SELECT user_id FROM posts
          WHERE topic_id = (select topic_id
           FROM topics
          WHERE forum_id = f.forum_id
            ORDER BY last_post DESC LIMIT 0, 1) ORDER BY date_line DESC LIMIT 0, 1
            ) AS last_poster_id 
     , (SELECT u.username FROM posts p
           LEFT OUTER JOIN users u
            ON p.user_id = u.user_id
          WHERE p.topic_id = (select topic_id
           FROM topics
          WHERE forum_id = f.forum_id
            ORDER BY last_post DESC LIMIT 0, 1) ORDER BY p.date_line DESC LIMIT 0, 1
            ) AS last_poster
  FROM forums f 
LEFT OUTER JOIN topics t 
    ON f.forum_id = t.forum_id 
LEFT OUTER JOIN posts p 
    ON t.topic_id = p.topic_id
GROUP BY f.forum_id
     , f.category_id
     , f.forum_name 
     , f.display_order
ORDER BY f.display_order

 

It seems to be working fine but the idea of the 3 sub queries within, seems abit long winded. I was just wondering if there was an easier and more efficient way. Appreciate the help. Thanks

[fenway]

Yikes... you can write your first subquery more cleanly, IMHO, as follows (UNTESTED):

 

(SELECT post_id FROM posts WHERE (topic_id,date_line) = ( SELECT topic_id, MAX(last_post) FROM topics WHERE forum_id = f.forum_id GROUP BY topic_id ) ) AS last_post_id

 

You could do something similar for the other queries as well. BTW, I find it a little strange that you're storing a last_post DATETIME value in the topics table itself... you could always find out this value with a query at run-time; though it does make the above query kind of simpler.

 

Unfortunately, you can't also just pull the user_id from the posts table in this query as written, since this type of subquery can only return a single operand; however, if you were to make a derived table, you could get combine it and retrieved both desired values.

 

Hope that helps & makes some sense.