Left Join Across Two Databases

Anon

I need to do a left join across two databases. Is this possible in MySQL? If so, how?

Thanks.

[deleted]

How about loooking at the manual? 🙂

http://www.mysql.com/doc/J/O/JOIN.html

Anon

Upgrade to a real database server and you might be able to do that

Anon

Just prepend the DB name to the table selecting from:

SELECT table_1_from_db_connected_to, other_db_name.table_2 ...

And make sure that the user for the first DB has permissions for the second
DB.

smarlowe

Actually, joining tables across different databases is slow, innefficient, and there are plenty of "real" dbms systems that can't do it.

Anon

Yea, I was worried about that problem. I've got it working last night, but worried that later on, I need to move up to another package, possibly Sybase and end up rewriting the code.

What my boss wants is a seperate database for users and their data (location, real name, nick, hobbies, etc.)

Then for each main section of the portal, boards, news, etc, they each have their own database.

That way we won't have 1 large database with users, forum data, news data, etc. all together.

In your opinion though, how'd you go about this? Would you place all the data on to one database?

Anon

Thanks, I've figured it out last night. Didn't know that I could prepend the database name before the table name to access that particular table. =)

smarlowe

Here's the problem with that. One of the key points of using a database is you can use transactions to ensure that either every change got made, or none of them got made and an error gets thrown to the right place to let you or the user know.

Generally related information should reside in the same database. If you decide to run several databases, the only way to make it transportable via ODBC or abstraction is to use things that should work on most databases.

Connect to messages db and users db. insert message into messages db. update user info. User info update fails.

How do you recover from this? The message is posted, but the user profile, maybe with links to the user's messages, didn't get updated. You the programmer have to go and roll back the first insert into messages to fix things.

Put them into the same database:

begin;
insert into table1;
delete from table2;
update table3;
update table4
commit;

If one fails, they all fail. You just throw an error telling which one failed.

The idea of putting your data into all these different databases may seem like it's helping, but in fact it may make your system crawl if you design it around a multiple database system.

Hard drives are VERY cheap. A 1 terabyte raid array is $23,000 nowadays. Putting all the data in one database isn't a problem for any real dbms system, including mysql. If you think a 23,000,000 row database is slow, just wait till you run an outer join against it from another 23,000,000 row database. ugh.

I'd much rather have the two large tables in the same database, where indexing is sure to work from one product to the next in a predictable fashion.

Your boss wants you to build a brittle system using proprietary dbms calls where none would normally be needed if not for his scheme. Sometimes you gotta just tell them the truth, that their idea is not the best way to do it, and here I don't think it is.