Custom Forum Query

Question

Hello,

I'm working on a custom made forum and was wondering if I could get some others ideas and opinions on the matter.

I was wondering what would be the most efficient MySQL query to achieve the following.

1. Find all Categories. These will list their parent forum_id as "0".
2. Find all Forums. These will list their parent forum_id as the categories ID.
3. Find all Sub-Forums. These will list their parent forum_id as the forums ID.
4. Find the total number of Posts for each forum (including their sub-forums).
5. Find the total number of Threads for each forum (including their sub-forums).
6. Find the last post information (time, thread, poster) for the Forum (including their sub-forums).

Is this possible?

Answer 1

With all the forum software that's already been created, it's unlikely that rolling your own is the best answer. Without knowing the table structure supporting your forum, it's pretty hard to give advice on queries, other than saying "yes, they're possible". But the best way to search would involve something like Sphinx.

Answer 2

You need to more fully separate your query design from your application (and table) design. You are listing (at least) six queries there, most of which would be ideally generated by an ORM.

I can also see you're probably falling prey to the most common forum software mistake: non-scalable table design. Most forum software stores posts with references to their parent posts, threads and forums. This means the number of queries per thread is directly proportional to the thread depth. I've seen forum software die because of this limitation. The most efficient way to store threads is a nested set model.

That leads me to the next question: what are you trying to achieve by writing forum software? Do you have a need for a forum? Why not install one of the many implementations out there (despite their flaws)? Or are you after a learning exercise? Or need forum software and it has to be written in-house (this is unusual: most corps will buy something rather than do that)?