David - the way Apollo does it is as follows:
I have a
user_board_view_history table and a
user_thread_view_history table. They are small, Fixed Length MySQL tables that consist of very little by references:
For the Board one:
user_ref - int(4)
forum_ref - tinyint
board_ref - tinyint
marked_read - datetime
For the Thread one:
user_ref - int(4)
thread_ref - int(4)
board_ref - tinyint
last_read - datetime
last_view - datetime
When a user views a thread they have never seen before it will add an entry into the thread_history table recording the last_read date and the last_view date. last_view is the actual time the user viewed the thread, last_read is the timestamp of the LAST POST the user read in that thread.
In order to curb a massive table building up, when-ever someone marks a BOARD as being read - I will run a DELETE query on the Thread History, removing any views of threads in that given board prior to the current date. This automatically keeps your thread_history table clean.
Even if no user ever did that, you're still only talking about a very tiny table size anyway. My thread_history table for example will only take up 14KB of database size per 300 records. So even at 5000 records that's only 200KB - and remember, it is a FIXED Length table, so MySQL will index and seek on that extremely quickly. You could also have a cron job scheduled to automatically purge this table every 30 days if you wanted (i.e. auto mark-as-read threads older than say 90 days).
Because I have an index on the user_ref combined with the thread_ref, I can do a SELECT .. IN () query on the table, passing in the ID of all threads displayed on the current page and it'll bring back the results in mere miliseconds. It need only be 1 query in total to get this "last viewed" information.
The reason I have a last_read and a last_viewed is that the last_read date stores the actual date of the most recent message read in that thread - whereas last_viewed stores the last time the user actually viewed the thread. If you don't do it like this, you will mark a whole thread as "being read" if they only look at the very first page of it, when in actual fact the "un-read" part of it might be on a say page 3 or 4.
If you want any more info, shout
Cheers,
Rich
With our species on the edge of extermination,
with no prospect but a horrible death,
we actually played games.
