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
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:
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 )
You see the magic? The tables above, and especially
_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