Divide database into one global and multiple subs?

[Pär Iwarson]

We have a site where we use MySQL 4.0.21 as database server. The site is a community where we within the community have subcommunities. The subcommunities have nothing to do with each other and will never have any activities in common.

There are a lot of activity on the site and as we are going to release this site globally we will get a whole lot more activity on the site.

The question I have is we would gain performance on splitting the database into one global database and multiple sub databases within the same database server.

My thoughts are that we should have one database with all tables than are used in the "global" community and then have some other databases where we have all tables that are specific for a subcommunity. These tables are only used within a subcommunity. A subcommunity will then have their information in a specific database along with a few other subcommunites but some other subcommunities will have their information in another specific database.

Some tables in the global database would be used quite frequently with the subspecific tables though. For example the userinfo table. This means that we will need to do quite a lot of cross-database queries between the global database and the subspecific database but never between 2 subspecific databases.

Some of tables that are specific for the subcommunities are pretty big and have millions of rows. Therefore we would like to reduce these tables by dividing them into separate databases that only contains the information for a specific subcommunity or at least only for subcommunities in a certain category.

I can say that we have some tables that currently contains about 500000 rows and is very frequenty used in selects, updates and inserts. When we release the site globally we expect this table to contain about 20 million rows. This is a table that has a lot of indexes and need to be sorted and handle in several ways. If this table contains 20 million rows I guess that it will become a performance problem. But if we can divide this table, specific for the subcommunities, into several databases we probably avoid this potenial performance problem.

I would be glad if someone had some thoughts and opinion on this database architecture. Is this something you would recommend or is this something we should avoid.

I hope you understand our problem.