Reply to thread

If I was writing a post moderation/deletion feature I wouldn't do it the traditional way - i.e. leaving the post in the posts table and changing a field to mark it as hidden. Under the assumption that most people won't have the permissions to view the post, I'd delete it from the DB, put it in a different table and use a UNION query to allow people with the appropriate permissions to see it. That way, the extra load is only on requests made by moderators and admins, instead of having a deeper index to deal with.

So, the post_num query might even work, unless you're a mod or an admin, in which case you'd have to do something totally different - but perhaps that's fine. In fact, if you were crazy enough, you could go completely to town with it and have a joint primary key on the table with thread_id and post_id - just set post_id to auto_increment and it happily starts a new thread at post_id 1, all by itself... but you'd need some pretty heroic code to make that work system-wide. I wouldn't want to write the split/merge thread features for it anyway.