Joining MySQL tables across multiple databases

I ran across a scenario today in which I needed to purge records from a table based on what was in a similar table in another database.

I had 2 user tables in different databases but the tables had some overlapping data. For example, the first user table is in a database called "database1" and the other is in a database called "database2." Some example data is below.

database1.users

IDName

1Jack 3Jim 5Mary 7Sue 9Ron 11Phil

database2.users

IDName

1Jack 2Creed 3Jim 4Dave 5Mary 6Bob

Now, to purge our user table in database1 of any records that match the user in database2, we first need to figure out how to write a join statement across multiple database. Here's how it's done.

SELECT u1.* FROM database1.users u1 LEFT JOIN database2.users u2 ON u1.id=u2.id WHERE u2.id IS NOT NULL;

The above query will will join the 2 tables based on the primary id and only return records for the first user table if a match is found in the second user table.

It's always a good idea, before running a DELETE statement to first test your query with a similar SELECT statement. Once you've confirmed your SELECT statement is returning the correct records you can modify it to delete those records returned.

DELETE u1.* FROM database1.users u1 LEFT JOIN database2.users u2 ON u1.id=u2.id WHERE u2.id IS NOT NULL;

When I first tried the DELETE query I kept getting an error because I started the query with DELETE FROM. I'm guessing that made the table to delete from ambiguous, because it worked fine when I made the delete statement more specific with DELETE u1.* FROM.

Tags:

Comments

Join query from multiple-databases

Hi Nathan,

I am running a website with PHP/MySQL and this will be a growing eBusiness in future. We have a limitation on our host that doesn't allow the db size to be more than 100MB. For this reason I'm designing to put a large table in a separate database for ease of maintenance in future (have bigger size, backing-up, archiving and making union etc)

As there is a relation between other tables and this one, do you think I am loosing any performance retrieving my join queries? Will they be much faster if I use all related tables in one database?

Do you have any suggestions for this architecture?

Thanks

I'm not too sure about the

I'm not too sure about the performance implications of joining tables across databases. That's outside the scope of my expertise.

plan to segregate tables in multiple databases

Hi Milad ,

As per your post i like to know your experience related to the performance when you have created multiple databases to maintain max limit of database.

I am working on a project which has a huge database. i want to separate the tables to multiple databases. is that affects the performance or it will decrease the response time.

is it possible to make relation between the attribute belonging from one DB with the attribute of other DB?

Pl send me the detail suggestion and the way how i can do it.

Thanks in advance.

mail me the details on

mail me the details on bhmb2000@rediffmail.com