Stack Overflow database design join issues

Question

There are many questions already on meta-stackoverflow regarding the database schema, but here is another, that I don't think is a duplicate, so please read on.

I read a description of the schema after I read this article on the High Scalability website. To quote the article, in the "Lessons learned" section:

Stack Overflow copied a key part of the Wikipedia database design. This turned out to be a mistake which will need massive and painful database refactoring to fix. The refactorings will be to avoid excessive joins in a lot of key queries.

As a newbie to database design, I thought it would be highly enlightening for somebody to expand on this statement a little, giving, for example, 1 or two examples of queries used by Stack Overflow that suck when it came to excessive joins.

Also, I'm thought I read somewhere (can't remember exactly where, so maybe I didn't) that the database had recently undergone some refactoring which, I am assuming, was to address the issues regarding the joins. Is the, what seems to be widely published, schema (in the link above) a description of the database before or after refactoring?

I'm not looking for a complete description of all the details about this, but some examples, general insights or links to other material would be a very useful resource for those of us who are new to this.

Answer 1

One point here is that it's not just joins that were a problem... it was excessive joins. It sounds like they had queries that needed to hit a lot of different tables, and they de-normalized the schema to avoid the need to touch of few of them.

I can only speculate, but an example might be that Wikipedia doesn't show authorship right away, but StackOverflow does. And so to display a question, stackoverflow has to join to the users table where Wikipedia might not.

Personally, I would have tried to handle the problem with an indexed view for that query instead, but then again maybe they tried that and it made too much difference on the update queries.

Answer 2

Another thing I noticed in mediawiki's scheme a few years back is that the contents of the post is separated from the metadata of the post. I remember seeing that it was to save space if two pages had the same content.

Obviously that doesn't happen much on any of these sites, so I'd speculate that could be a painful spot.

Answer 3

I also wonder why not considering other approaches. I once read a very interesting article about programming in LISP, entitled "Beating the Averages", by Paul Graham. It not only uses no regular scripting, but it also uses no database for building an online store! I'm not sure if it's still designed like that up to today, but viaweb is still around just it's been called Yahoo Store for over 10 years and it's definitely possible to build stuff with no database nowadays. I just don't know about large scales.

there's no database here http://www.opinion8r.com/images/no_database.png

A database always seemed to me like a flawed concept of building something to manage data on top of something that's already build for that (a.k.a. the file system)! Sure you could come up with many different arguments, such as it's made for distributing data across systems, rather than managing how it's physically stored; it's software oriented rather than hardware; and so on. And I agree with it, but it still seems like there's a lot of room for improvement in that aspect, and so I was stunned by knowing that it was actually possible to build such a dynamic web site without a database. And I'd love to also see something like StackOverflow following that same path with success.