Before joining Automattic, the company I worked for had a CMS with multisite capabilities. Generally, the database contained the same number of tables regardless the amount of sites created. There was a table Sites and most of the rest of the tables had also a cross reference table eg Products and SiteProducts. Since all the multisite installations belonged to the same organization, having cross reference tables is quite powerful cause you can easily share content (posts, products, banners etc) between sites without duplicating which also allows for central management. On the other side, there were some drawbacks too. It wouldn’t scale enough, database could get pretty big and splitting it in two different machines was not straightforward.

That’s the reason I am so inspired of the fact that WordPress.com can handle million of sites in a single installation. How does it handle it?

WordPress database architecture for multiple sites

WordPress has a blogs table ( much like the Sites table mentioned above ) which keeps records of all the sites of the current installation ( only for multisite enabled installations ). So an entry is created there for starters. Then, while we keep the following set of tables for the main site:

• wp_commentmeta
• wp_comments
• wp_links
• wp_options
• wp_postmeta
• wp_posts
• wp_terms
• wp_termmeta
• wp_term_relationships
• wp_term_taxonomy

we create a new set of tables for each new site. Each table contains the blog_id of the new site ( in this case blog_id = 2 )

• wp_2_commentmeta
• wp_2_comments
• wp_2_links
• wp_2_options
• wp_2_postmeta
• wp_2_posts
• wp_2_terms
• wp_2_termmeta
• wp_2_term_relationships
• wp_2_term_taxonomy

You see the magic? The tables above, and especially _options and _posts are usually the biggest ones with hundreds or thousands of records for even a single site. Using a separate set of tables for each new site:

• allows us to access ( read / write ) database data faster
• easily migrate a site into / out of the multisite installation