According to this, stackoverflow uses one table for both questions and answers. This design seems somewhat unusual.

What SQL query does stackoverflow use to retrieve question #123456 and all its answers?

What is the advantage of this design compared to having separate tables for questions and answers?

  • This belongs on Stack Overflow proper, IMO. – Jon Seigel Nov 17 '10 at 18:20

You probably won't get the exact SQL query, since only the SE team knows that. However, the basic form probably looks something like

SELECT * # Replace the actual fields
FROM posts 
WHERE (Id = {POST_ID}) OR (ParentId = {POST_ID})

The reason one table is used is because questions and answers are actually very similar. Both can be voted upon, have authors and editors, etc.. Keeping it in one table makes things easier for the DBAs.

In theory, it might also save a JOIN, but since SE redirects to a question page even if you pass an answer ID as a question ID, it might not necessarily do so.

  • Thanks. The WHERE clause of (Id = {POST_ID}) OR (ParentId = {POST_ID}) seems potentially inefficient. Would it be able to use index? – Continuation Nov 17 '10 at 1:05
  • @Continuation As wai said, its a guess on what they use, not exactly. Going to have to wait for one of the mods to give an exact answer – TheLQ Nov 17 '10 at 1:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .