What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 131 Stack Exchange communities.

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?

share|improve this question
    
This belongs on Stack Overflow proper, IMO. –  Jon Seigel Nov 17 '10 at 18:20

1 Answer 1

up vote 1 down vote accepted

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})
ORDER BY PostTypeId ASC, Score DESC

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.

share|improve this answer
    
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 .