Jump to content


Photo

SQL Efficiency


  • Please log in to reply
1 reply to this topic

#1 jgd12345

jgd12345
  • Members
  • Pip
  • Newbie
  • 6 posts

Posted 02 January 2006 - 12:28 PM

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

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 02 January 2006 - 07:13 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users