Unfortunately no one can be told what FluxBB is - you have to see it for yourself.

You are not logged in.

#1 2012-07-30 01:35:25

Registered: 2011-11-05
Posts: 350

Shouldn't this be optimized?

Don't get me wrong, I might be wrong but I am going to go for it anyways.

This code here

$result = $db->query('SELECT t.forum_id,, t.last_post FROM '.$db->prefix.'topics AS t INNER JOIN '.$db->prefix.'forums AS f ON LEFT JOIN '.$db->prefix.'forum_perms AS fp ON ( AND fp.group_id='.$pun_user['g_id'].') WHERE (fp.read_forum IS NULL OR fp.read_forum=1) AND t.last_post>'.$pun_user['last_visit'].' AND t.moved_to IS NULL') or error('Unable to fetch new topics', __FILE__, __LINE__, $db->error());

	$new_topics = array();
	while ($cur_topic = $db->fetch_assoc($result))
		$new_topics[$cur_topic['forum_id']][$cur_topic['id']] = $cur_topic['last_post'];

	$tracked_topics = get_tracked_topics();

in index.php

pretty much get's all the id's of the forum in a multi demi array right? (That you read)

then later in Index.php fluxbb uses this code to fetch if the forum was read or not or if they read the topic in that forum:

if (!$pun_user['is_guest'] && $cur_forum['last_post'] > $pun_user['last_visit'] && (empty($tracked_topics['forums'][$cur_forum['fid']]) || $cur_forum['last_post'] > $tracked_topics['forums'][$cur_forum['fid']]))
		// There are new posts in this forum, but have we read all of them already?
		foreach ($new_topics[$cur_forum['fid']] as $check_topic_id => $check_last_post)
			if ((empty($tracked_topics['topics'][$check_topic_id]) || $tracked_topics['topics'][$check_topic_id] < $check_last_post) && (empty($tracked_topics['forums'][$cur_forum['fid']]) || $tracked_topics['forums'][$cur_forum['fid']] < $check_last_post))
				$item_status .= ' inew';
				$forum_field_new = '<span class="newtext">[ <a href="search.php?action=show_new&amp;fid='.$cur_forum['fid'].'">'.$lang_common['New posts'].'</a> ]</span>';
				$icon_type = 'icon icon-new';


Isn't there a way to store the forum id's in the global topic id tracked cookies already? Or, just store forum id's in a separate cookie or something? Then use the same way how new topic posts are treated.  (no need to use that query) just read from cookies.

TLDR; Can't we just use the same way how viewforum.php treats new posts/topics (Extracting from cookie) instead of pulling the data and running a query for index.php? If so, I think this should be a official performance trick and could save 1 query, which is suitable to go in the official version. (IMO)

Last edited by Newman (2012-07-30 01:37:16)


#2 2012-08-31 15:31:08

New member
Registered: 2012-08-28
Posts: 3

Re: Shouldn't this be optimized?

Of course you are right Newman, I just discovered the same problem, I wanted to test the performance of fluxbb for a large forum, so I Generated around 1 million topics and 10 million posts using a PHP script, and then when I try to load  the index.php it took around 14 seconds, and my mysql-slow-query log shows the  same query.

# Time: 120831 20:41:29
# User@Host: root[root] @ localhost []
# Query_time: 14.367822  Lock_time: 0.003000 Rows_sent: 1038842  Rows_examined: 1038846
SET timestamp=1346425889;
SELECT t.forum_id,, t.last_post FROM topics AS t INNER JOIN forums AS f ON LEFT JOIN forum_perms AS fp ON ( AND fp.group_id=4) WHERE (fp.read_forum IS NULL OR fp.read_forum=1) AND t.last_post>0 AND t.moved_to IS NULL;

it examined 1038864 and returned 1038842 results.

However, if I try to access the homepage as a guest user page loded within 2 seconds.

I thought that it because I generated all posts in a single forum.  But now I can understand the problem. Yes I think developers must work to solve this problem. I googled the first part of my slow query and found your topic. Thanks for your post it really helped me.


#3 2013-01-13 18:40:39

Lead developer
From: Germany
Registered: 2008-05-13
Posts: 6,737

Re: Shouldn't this be optimized?

I finally created a ticket for this - should be fixed in the next maintenance release. | develoPHP

"As code is more often read than written it's really important to write clean code."


#4 2013-10-12 07:15:38

From: California
Registered: 2008-05-09
Posts: 929

Re: Shouldn't this be optimized?

I must be dense since I don't see the purpose of tracking 1,000,000 unread topics in the example provided in posting #2. No one will read all the unread topics in one session. The tracking will be cleared/reset after the session is over. Why not limit tracking up to one month? Please enlighten me.


Board footer

Powered by FluxBB