• 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?

Brent W

Well-known member
#1
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???
 
R

ragtek

Guest
#2
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
 

Brent W

Well-known member
#3
This is the query in the add-on:

PHP:
        //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) $exclindexpostforums
            AND post.message_state = 'visible'
            AND user.is_banned = 0
            GROUP BY post.post_id
            ORDER BY post.post_date DESC
        ",$limit);
 

x3sphere

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

ex.

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

ragtek

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