1. 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

Discussion in 'General PHP and MySQL Discussions' started by i_n_k, Sep 5, 2016.

  1. i_n_k

    i_n_k Member

    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"
     
  2. Mike

    Mike XenForo Developer Staff Member

    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 likes this.
  3. i_n_k

    i_n_k Member

    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. i_n_k

    i_n_k Member

    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_n_k

    i_n_k Member

    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
     
  6. Mike

    Mike XenForo Developer Staff Member

    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 likes this.
  7. i_n_k

    i_n_k Member

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

Share This Page