Jump to content

Archived

This topic is now archived and is closed to further replies.

jgd12345

SQL Efficiency

Recommended Posts

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.