Home

Your premium source for custom modification services for phpBB

  logo

HomeForumsBlogMOD ManagerFAQSearchRegisterLogin

Comments February 28, 2008

Tuning the “Newest Post Since Last Visit” SQL

Filed under: Performance Tuning, phpBB — Dave Rathbun @ 11:58 pm CommentsComments (1) 

Most phpBB users really like the “orange thingie” that takes them to the newest post in a topic. (This feature, like many, is only available to users that are logged in.) The image is this one: I like it too. I like it even more since I optimized the query that drives this functionality. :)

Edit: This post turns out to be wrong. I am leaving it in place, but the code suggestions made here are not effective and in fact will generate SQL errors in certain cases. More details are in the first comment at the end of the post.

When a board member clicks that icon, it takes them to the first post made since they started their session. (It really would be nice if it went to the first unread post; stay tuned for more on that idea.) Here is the query that is used to accomplish this magic:

$sql = "SELECT 	p.post_id
	FROM 	" . POSTS_TABLE . " p
	, 	" . SESSIONS_TABLE . " s
	,  	" . USERS_TABLE . " u
	WHERE 	s.session_id = '$session_id'
	AND 	u.user_id = s.session_user_id
	AND 	p.topic_id = $topic_id
	AND 	p.post_time >= u.user_lastvisit
	ORDER BY p.post_time ASC
	LIMIT 1";

Time to disect a query. First, the last line has a “LIMIT 1″ clause. That makes sense, because I only ever want to return a single value from this query. What is the value I am trying to select? The post_id of the newest post since my session started. What information do I need to know in order to do that?

  • I need to know which topic that I’m looking at. That is provided as the paramter $topic_id and it relates to the topic link that I clicked.
  • I need to know who I am. :) That is done in this query by checking my session and seeing which user_id is attached to that session.
  • I need to know how recently I logged in. That is checked using the user_lastvisit column of the phpbb_users table.

So it seems fairly easy to justify the query used, as it uses three different tables to determine those bits of information outlined above. But wait, are all three tables really needed? The posts table makes sense, the users table is used to get the user_lastvisit value, and the sessions table is used to… do what, exactly?

Session Check

Why do I need the sessions table as part of this query? After further review, I don’t think that I do. In the current sql code I would call the session table the “driving table” for this query. That just means that it is used first in the process flow. The current session_id is a parameter to the query.

After getting my session information, my user_id is determined from the row in the sessions table.

This doesn’t make sense. :) I already know the user_id, since it’s in the $userdata array. I don’t need to start with the session table in order to get the session_user_id value. Right before this query is a check to make sure that the session_id exists and is valid. If there is no session, then we don’t do anything with this code anyway!

So I dropped the sessions table from the SQL and ended up with this:

$sql = "SELECT 	p.post_id
	FROM 	" . POSTS_TABLE . " p
	,  	" . USERS_TABLE . " u
	WHERE	p.topic_id = $topic_id
	AND 	p.post_time >= u.user_lastvisit
	ORDER BY p.post_time ASC
	LIMIT 1";

Ok, so this has to be more efficient as there are only two tables instead of three, which means less database I/O. The query is still only returning the oldest post that is newer than the user’s lastvisit time for this topic., and it is getting that lastvisit value from the users table.

Getting the Last Visit Time

But wait… all of the data from the phpbb_users table for this particular user has already been loaded into the $userdata array prior to even getting to this point. Everything – including the user_lastvisit data – is included in the $userdata array! So there is no reason whatsoever to read the USERS_TABLE to get this information. Drop that table as well, and all that is left is this:

$sql = "SELECT 	p.post_id
	FROM 	" . POSTS_TABLE . " p
	WHERE	p.topic_id = $topic_id
	AND 	p.post_time >= " . $userdata['user_lastvisit'] . "
	ORDER BY p.post_time ASC
	LIMIT 1";

There is still a reference to the user_lastvisit, it’s just that it’s coming from an array already in memory rather than a table in the query.

I took a query with 3 tables and the appropriate joins and reduced it down to a query with a single table and no joins. Does it work?

Conclusion

In my testing it worked, and it worked perfectly. The “orange thingie” as I like to call it continued to send me to the first post made in the selected topic since my last visit even with this much less complex and much more efficient SQL. I don’t see any reason not to apply this change, and have written it up as a MOD. However, rather than stop here I added one more feature that changes the very nature of the “orange thingie” and should make it much more popular. Instead of taking a viewer to the first unread post since they last logged in, it takes them to the first unread post since they last read the topic. It works this way in phpBB3, so there’s no reason why not to have the same thing in phpBB2, right? :)

I’ll try to post the full code for that MOD shortly. Until then, you can consider making this small query tweak to viewtopic.php and see if you gain any benefit.

1 Comment

  1. Hm. Well. This post is wrong. :) I tried to take part of a solution and use it, but it seems that as written this code will not work. The reason those extra tables are required is because the $userdata array has not been populated yet when this code is executed. So that means that you have to use the session table to get the user information, and you need the user information to get the last visit date/time value correct.

    I could move the session code up above this, but that adds extra overhead that goes beyond using the extra tables in this query. So I would NOT suggest that you make the changes as listed, as they will not work. Live and learn. :)

    Comment by Dave Rathbun — February 29, 2008 @ 10:16 am

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Powered by WordPress