Better Way to Join Tables

[i_n_k]

I have a huge forum and I'm trying to run the below query and it's bogging things down pretty horribly. I'm assuming knowing how bad I am when it comes to table joins that there has to be a better way to do this to not raise the load on the server, so any help is appreciated.

SELECT xf_thread.thread_id, xf_user.avatar_date, xf_post.user_id, xf_post.post_id, xf_post.username, xf_thread.title, xf_thread.node_id FROM xf_post LEFT JOIN xf_thread ON xf_thread.thread_id=xf_post.thread_id LEFT JOIN xf_user ON xf_post.user_id=xf_user.user_id WHERE xf_thread.node_id = '10' ORDER BY xf_post.post_id DESC LIMIT 0,1"

 

[Mike]

Am I correct in thinking that you're trying to get information about the most recent post in a particular forum? If so, it's no so much the joins as the way the query was written. You'll get better performance reading from the xf_thread table and sorting by last_post_date and then reading the last_post_id and/or last_post_user_id values to go to the post/user tables.

 

[i_n_k]

Thanks, performance is definitely better

I went with this and the result seems to be working - unless you can think of another way to tweak it a little better?

SELECT xf_thread.thread_id, xf_user.avatar_date, xf_post.user_id, xf_post.post_id,xf_post.username, xf_thread.title, xf_thread.node_id FROM xf_thread LEFT JOIN xf_post ON xf_thread.thread_id=xf_post.thread_id LEFT JOIN xf_user ON xf_thread.last_post_user_id=xf_user.user_id WHERE xf_thread.node_id = '10' ORDER BY xf_thread.last_post_date DESC LIMIT 0,1

 

[i_n_k]

Actually it's not showing the latest post - it's a couple behind somehow...guessing I'm not sorting it quite right with that formula...

 

[i_n_k]

I think I got it:

SELECT xf_thread.thread_id, xf_user.avatar_date, xf_post.user_id, xf_post.post_id,xf_post.username, xf_thread.title, xf_thread.node_id FROM xf_thread LEFT JOIN xf_post ON xf_post.post_id=xf_thread.last_post_id LEFT JOIN xf_user ON xf_user.user_id=xf_thread.last_post_user_id WHERE xf_thread.node_id = '10' ORDER BY xf_thread.last_post_id DESC LIMIT 0,1

 

[Mike]

The "ORDER BY xf_thread.last_post_date" query is more correct, assuming you're looking for visible posts. Using last_post_id isn't guaranteed to be correct (a higher post_id is not necessarily newer) and it won't be have the performance benefits of last_post_date.

 

[i_n_k]

The "ORDER BY xf_thread.last_post_date" query is more correct, assuming you're looking for visible posts. Using last_post_id isn't guaranteed to be correct (a higher post_id is not necessarily newer) and it won't be have the performance benefits of last_post_date.

Good deal, I just made the change. Appreciate you jumping in and helping me out, much appreciated