• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Better Way to Join Tables

#1
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. :)

Code:
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

XenForo developer
Staff member
#2
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.
 
#3
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?

Code:
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
 
#4
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... :confused:
 
#5
I think I got it:

Code:
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

XenForo developer
Staff member
#6
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.
 
#7
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 :)