Join tables across database

MySQL & MariaDB Other MySQL® Questions
1

Hi there…

I am building a couple of websites. The idea is that the two (later to be more) sites share some info such as user accounts.

Now, I want to use a separate database for each website, but how would I manage to link the user tables?

Currently all the databases exist on the same server, so I beleive it would be possible to keep the joint data in its own database and be able to access it in database.table.column notation.

However, would I pay a performance hit doing it this way? In particular if I were performing joins across databases?

Is there another way to do it. I.e. could I create a virtual table in one database that is a linked copy one from the other database.

Also, if the database grows beyond a single server, I guess I could use replication to do this?

2

Just had a thought. Might be crazy, but:

I have two databases, A and B that have application specific data. I have database C that has shared data (users).

Could I use federated tables from database A or B to connect to the tables in database C? Can I do this even though all databases exist on the same mysql instance?

3

Hm,

If it is same instance you can simply join tables from different databases such as

select * from db1.tbl1 join db2.tbl2 on tbl1.id=tbl2.id