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

XF 1.1 Impossible To Get Latest Posts Efficiently?

Discussion in 'XenForo Questions and Support' started by BamaStangGuy, Aug 2, 2012.

  1. BamaStangGuy

    BamaStangGuy Well-Known Member

    So I recently purchased this add-on: http://xenforo.com/community/resources/recent-posts-forum-index-smf-style.392/

    I have been unable to use this add on due to the query in it taking over 10 seconds to run. See here: http://xenforo.com/community/threads/recent-posts-forum-index-smf-style-paid.28404/

    After doing a little research I noticed that ragtech also brought this up here: http://xenforo.com/community/threads/what-is-the-best-recent-posts-add-on.31721/#post-365068

    So is it impossible to get a fast query to simply show the latest 5 posts on the forum???
  2. ragtek

    ragtek Guest

    probably it's possible with some query optimization:) /that's what at least helped my query a little bit)

    but i'm doing it now completly different...
    fetching the data from the post table is too "expensive" :D
  3. BamaStangGuy

    BamaStangGuy Well-Known Member

    This is the query in the add-on:

            //Get the latest posts
    $recentindexPosts $this->limitQueryResults("
                SELECT post.*, thread.*, user.*,
                forum.title AS node_title, forum.node_id AS node_id
                FROM xf_post AS post
                LEFT JOIN xf_thread AS thread ON (thread.thread_id = post.thread_id)
                LEFT JOIN xf_user AS user ON (user.user_id = post.user_id)
                LEFT JOIN xf_node AS forum ON (forum.node_id = thread.node_id)
                WHERE NOT ISNULL(thread.thread_id) 
                AND post.message_state = 'visible'
                AND user.is_banned = 0
                GROUP BY post.post_id
                ORDER BY post.post_date DESC
  4. x3sphere

    x3sphere Active Member

    Not sure if it's the best approach but you can add a constraint to post_date


     SELECT post.*, thread.*, user.*,
                forum.title AS node_title, forum.node_id AS node_id
                FROM xf_post AS post
                LEFT JOIN xf_thread AS thread ON (thread.thread_id = post.thread_id)
                LEFT JOIN xf_user AS user ON (user.user_id = post.user_id)
                LEFT JOIN xf_node AS forum ON (forum.node_id = thread.node_id)
                WHERE post.message_state = 'visible'
                AND forum.node_id != 3
                AND user.is_banned = 0
    AND post.post_date >  1343624400
    ORDER BY post.post_date DESC
    LIMIT 10
    Will get last 10 posts since July 30. That query took 0.5 secs for me on a 220K post table. Instant afterwards due to cache.
  5. ragtek

    ragtek Guest

    my exotic way because i had to code it for a big customer with > 1 000 000 posts..
    cache the last 100 posts (including all necessary data) in a own table and fetch the data from there instead of the very big post table...:)

Share This Page