[eluser]jedd[/eluser]
Ah, I'm sorry - I misread. I thought you were asking how you identify from a list of threads - ie, within a forum - which ones have unread messages.
Okay .. well, what do you have so far? Do you have something similar to my message# / thread# / user# correlating table to track where users are up to within a thread?
If you do, then (outside of de-normalising your database fairly savagely) I suspect you're going to have to do what I do above - which I do per page of threads - to all the threads within a forum. Using my approach, I could see that becoming increasingly expensive, as I stop monitoring threads that are locked - so I'd have to check if they are old (and locked) or new (and not registered in my message/thread/user table yet).
Do you need to know the number of threads that contain unread messages within a forum, or just if
any threads with unread messages exist within a forum? The latter is obviously going to be cheaper to determine.