What does this mean?

toad

Managed Customer
Something is taking a long time with my forum. Does anyone know what this means?

Code:
SELECT COUNT(*)             FROM xf_post AS post
            INNER JOIN xf_thread AS thread ON               
(post.thread_id = thread.thread_id AND thread.node_id IN (12, 16, 93, 92, 138, 142, 97, 105, 104, 101, 98, 95, 112, 103, 99, 123, 94, 96, 132, 28, 107, 124, 63, 61, 52, 53, 17, 108, 72, 106, 109, 33, 137, 139, 122, 10, 11, 9, 135))             INNER JOIN xf_forum AS forum ON                
(forum.node_id = thread.node_id AND forum.find_new = 1)           
LEFT JOIN xf_thread_read AS thread_read ON               
(thread_read.thread_id = thread.thread_id AND thread_read.user_id = '2860')           
LEFT JOIN xf_forum_read AS forum_read ON               
(forum_read.node_id = thread.node_id AND forum_read.user_id = '2860')           
WHERE post.post_date > '1409232170'           
AND post.message_state = 'visible'          
AND post.post_date > GREATEST(               
IF (thread_read.thread_read_date IS NULL, 0, thread_read.thread_read_date),               
IF (forum_read.forum_read_date IS NULL, 0, forum_read.forum_read_date)             )
 
Last edited by a moderator:

Matt

Owner
Something is taking a long time with my forum. Does anyone know what this means?

SELECT COUNT(*) FROM xf_post AS post
INNER JOIN xf_thread AS thread ON
(post.thread_id = thread.thread_id AND thread.node_id IN (12, 16, 93, 92, 138, 142, 97, 105, 104, 101, 98, 95, 112, 103, 99, 123, 94, 96, 132, 28, 107, 124, 63, 61, 52, 53, 17, 108, 72, 106, 109, 33, 137, 139, 122, 10, 11, 9, 135)) INNER JOIN xf_forum AS forum ON
(forum.node_id = thread.node_id AND forum.find_new = 1)
LEFT JOIN xf_thread_read AS thread_read ON
(thread_read.thread_id = thread.thread_id AND thread_read.user_id = '2860')
LEFT JOIN xf_forum_read AS forum_read ON
(forum_read.node_id = thread.node_id AND forum_read.user_id = '2860')
WHERE post.post_date > '1409232170'
AND post.message_state = 'visible'
AND post.post_date > GREATEST(
IF (thread_read.thread_read_date IS NULL, 0, thread_read.thread_read_date),
IF (forum_read.forum_read_date IS NULL, 0, forum_read.forum_read_date) )
That's @Chris D's unread post count add-on.
 

Matt

Owner
PHP:
        public function getUnreadPostCount($userId, array $forumIds)
        {
                $autoReadDate = XenForo_Application::$time - (XenForo_Application::get('options')->readMarkingDataLifetime * 86400);

                if (!sizeof($forumIds))
                {
                        return 0;
                }

                return $this->_getDb()->fetchOne('
                        SELECT COUNT(*)
                        FROM xf_post AS post
                        INNER JOIN xf_thread AS thread ON
                                (post.thread_id = thread.thread_id AND thread.node_id IN (' . $this->_getDb()->quote($forumIds) . '))
                        INNER JOIN xf_forum AS forum ON
                                (forum.node_id = thread.node_id AND forum.find_new = 1)
                        LEFT JOIN xf_thread_read AS thread_read ON
                                (thread_read.thread_id = thread.thread_id AND thread_read.user_id = ?)
                        LEFT JOIN xf_forum_read AS forum_read ON
                                (forum_read.node_id = thread.node_id AND forum_read.user_id = ?)
                        WHERE post.post_date > ?
                        AND post.message_state = \'visible\'
                        AND post.post_date > GREATEST(
                                IF (thread_read.thread_read_date IS NULL, 0, thread_read.thread_read_date),
                                IF (forum_read.forum_read_date IS NULL, 0, forum_read.forum_read_date)
                        )
                ', array($userId, $userId, $autoReadDate));
        }
 

toad

Managed Customer
Does it work better? I haven't enabled it since i disabled it. But all that extra traffic i was getting is gone.
 

Matt

Owner
It does. It caches the number for a couple of minutes, rather than calculating it each time a page is refreshed.
 

toad

Managed Customer
great! I'll have to give it a shot again. I miss seeing the number each time i return.
 

toad

Managed Customer
I want to get Post Ratings by Luke but I heard that it might cause this sort problem too.
 

Matt

Owner
I want to get Post Ratings by Luke but I heard that it might cause this sort problem too.
If you read the description of the add-on, it should work fine on larger sites. We run it on TheAdminZone with just under 1million posts.
 
Top