Performance effect of joining tables from different databases

Question

I have a web site using a database named lets say "site1". I am planning to put another site on the same server which will also use some of the tables from "site1".

So should I use three different databases like "site1" (for first site specific data), "site2" (for second site specific data), and "general" (for common tables). In which there will be join statements between databases general and site1 and site2. Or should I put all tables in one database?

Which is the best practice to do? How performances differ in each situation? I am using MySQL. So how is the situation especially for MySQL?

Thanks in advance...

Ostrich

Am i right to assume the dbs are both on the same server? – Scutt

yes they are. And I have already tried joining tables from different databases. it works. – Ostrich

Answer 1

From the performance point of view, there won't be ANY difference. Just keep your indexes in place and you will not notice whether you are using single DB or multiple DBs.

Apart from performance, there are 2 small implications that I can think of: 1. You can not have foreign keys across DBs. 2. Partitioning tables in DB based on their usage or based on applications can help you manage permissions in easy way.

Breuer

My answer assumes that all the DBs are on the same DB server. If you keep them on different DB servers, you will start seeing performance degradation. – Breuer

You can have foreign keys across DBs in MySQL, if they are on the same server. But do not know what happens if they are on different servers or in other DBMSs. – Ostrich

Answer 2

I can speak from recent personal experience. I have some old mysql queries in some PHP code that worked fine with a relatively small database, but as it grew the query got slower and slower.

I have freeradius running mysql in its own database along with another management php app that I wrote. The freeradius table is > 1.5 million rows. I was attempting to join tables from my app's database to the freeradius database. I can say for sure 1.5 million rows is too many. Running some queries locked up my app altogether. I ended up having to re-write portions of my php app to do things differently (ie not joining 2 tables from different database). I also indexed the radius accounting table on some key fields and optimized some queries (mysql EXPLAIN statement is wonderful to help with this). Things are MUCH faster now.

I will definitely be hesitant to join 2 tables from different databases in the future unless really really necessary.

Cimah

You sure the slowdown wasn't because of the db size? You would have most likely seen the slowdown even if the tables were in the same db. – Darton