3

I am developing a website (WordPress MU - based). In this website, users can create their own blogs, can fully customize their own blogs (change background, write articles...), add more users to their blogs...

The most common query is to get articles of a blog. 2 different blogs are almost not related.

The information of the blogs can be stored in database in 2 ways:

  1. A few tables containing all the information, each blog is identified by blog_id.
  2. Many groups of tables. Each group of tables contains all information of a blog. More blogs, create more tables.

My questions are:

  1. In term of performance, which one is better?
  2. WordPress actually uses the second design. If the number of blogs is very large (thousands) then the number of tables can be tens of thousands. Does this cause any problem? Is there a limit of number of tables of a MySQL database?
Lightness Races in Orbit
  • 358,771
  • 68
  • 593
  • 989
Lucas
  • 174
  • 3
  • 11
  • this might enlighten you somewhat - http://stackoverflow.com/questions/4419499/mysql-nosql-help-me-to-choose-the-right-one-on-a/4421601#4421601 – Jon Black Sep 14 '11 at 18:38
  • Thank you f00, it's really helpful. I didn't find this before asking this question. By the way, do you have any idea about my second question? – Lucas Sep 15 '11 at 15:21

4 Answers4

4

Go for option #1 - it's called normalisation.

Adam Hopkinson
  • 26,835
  • 7
  • 57
  • 87
  • wp_blogs only stores blog ID, blog url... Most information of a blog is stored in tables created for that blog (e.g wp_1_posts, wp_1_users, wp_1_options...) – Lucas Sep 14 '11 at 19:10
  • 1
    My mistake. But you should definitely go for option #1! – Adam Hopkinson Sep 14 '11 at 19:30
3

For the sake of code maintainability, I beg you to go for option 1.

Adriano Carneiro
  • 53,285
  • 12
  • 85
  • 120
1
  1. They should both really perform very similarly, given appropriate indexing
  2. Managing a large number of tables is more problematic, especially with schema changes
Cade Roux
  • 83,561
  • 38
  • 170
  • 259
  • 1. I don't really understand what you meant by "given appropriate indexing"? Is the database indexed the same way (by MySQL) for both designs? I thought that WordPress uses the second design because the blogs are almost not connected, separating them into many tables might make it faster. 2. Yes you are right. I use phpMyAdmin and it doesn't work properly when the number of tables is more than 10000. – Lucas Sep 14 '11 at 19:18
  • @Jack When a table is properly designed and indexed, the size of the table has very little to do with the speed of retrieving (the same number of) records. That's the purpose of indexing - reducing the search time to an O(log n) operation, this grows slowly compared to n. Assuming that the table has a key which allows the index to be "partitioned" by blog first, the retrieval should be largely equivalent. It's usually a binary search. http://en.wikipedia.org/wiki/Twenty_Questions – Cade Roux Sep 14 '11 at 19:24
0

I did some searches and I think I saw more often people saying they would prefer one table with many data than many tables. They say it's easier to maintain.

Here is some texts of database normalization :

http://support.microsoft.com/kb/283878
http://www.phlonx.com/resources/nf3/

Good luck!

Dan D.
  • 67,516
  • 13
  • 93
  • 109
Mathieu Devost
  • 19
  • 7