PHP Mysql joins across databases

Question

Lets say I have two separate databases, X and Y, on the same physical server.

All of my queries currently run out of X.

I find I have one table in Y I would like to be available to X for JOINS.

So... now I keep a copy of the one table I need for both X and Y in both X and Y, but the data in Y is constantly changing, so the copy soon becomes out of date. Not critical for this application, but for another I'm designing now this just won't do.

X right now only needs read access to the table in Y.

Is there an efficient way to do joins across the two databases?

I'd prefer a mysql solution as opposed to opening multiple database connections in php.

Thank you.

EDIT: So what becomes of these lines?:

$conn = mysql_connect('localhost','username','password');
@mysql_select_db('database_name',$conn)

And yes.. this is an old app, hence the mysql instead of mysqli. Suppose I could convert it.

EDIT2: So where I don't specify database_name.table_name and just specify table_name I get whatever is selected, and where I do specify database.table_name I get what I asked for.. right? Yep. Works.

EDIT3: Is there any significant performance hit by doing cross database joins versus joins within the same database? Nope. Looks just as fast.

Thank you all for your responses.

Answer 1

you have to alias the table in the other database:

select a.id
     , b.foreign_id
  from database1.table1 a
  join database2.table1 b
    on b.foreign_id = a.id

Answer 2

If the 2 databases are on the same server, you can simply qualify the table with the database name in the join query.

select * from database1.table1 as t1
 inner join database2.table2 as t2
 on t2.fk_id = t1.pk_id