MySQL Performance: MySQL => Multiple databases vs. multiple tables

Multiple databases vs. multiple tables [message #616]

Sun, 21 January 2007 11:07

pastk
Messages: 1
Registered: January 2007

Junior Member

Hi!

I often see on this [exellent!] blog recommendations to follow multiple-table design (horizontal clustering in fact) when working with large data sets. In this way, for example, mail-boxes for different users are kept in several tables: inbox_001, inbox_002, sent_01, sent_02, etc.

The question is: why not use multiple databases for the same purpose?

Imagine maildb01.inbox, maildb01.sent, maildb02.inbox, maildb02.sent, etc.

Thus, we can have clustered data (spanning multiple tables) more logically structured.

Using this approach you can store the data, which is not meant to be clustered in, say, 'main' db, and the clustered data in multiple dbs.

As I know, at least in MySql tables and databases are pretty the same in technical terms, i.e. you can issue cross-database queries without any additional concern.

When you need to work with a particular data set you can just issue 'use db' before the real query. This may be especially useful if you need to implement clustering for production system - you don't have to rewrite much code to substitute table names (i.e., inbox_01) in your queries. But with a price of an additional query roundtrip (you can issue 'use db1; select col from table;' however).

It is also must be easier to manage: just 'drop database' instead of multiple 'drop tables'.

I can't see any major 'cons' here, except that this thing may not wotk with other RDBMSes.

Am I missing anything?

Report message to a moderator