multiple database select

[rallykarro]

Hi,

How do I at the best way perform select statements over multiple
databases?

I have a couple of databases containing the same table definitions with
diffrent data. Now I want them to act as a single database giving me
one answer on a select statement but the answer fetched from all my
defined databases.

Right now the databases are on the same node and I guess it is possible
to do something like:
select * from db1.table1, db2.table1 etc etc. but I really do not want
to modify all my sql statements if another database would comes into
picture or is revoved from the set.

One solution may be to create a view?!
I am using PHP and PEAR DB to access and query my databases.

thanks,

Karolina

[Gordon Burditt]

>How do I at the best way perform select statements over multiple

databases?

If you're talking about different databases handled by the same
server, you can use the databasename.tablename to refer to tables
outside the current database.

If you're talking about different databases on different servers
potentially on different continents, MySQL has the "Federated" table
type, which allows you to reference a table on another MySQL server
as though it was on this one. Requires at least MySQL 5.0, as I
recall. Don't expect it to be fast. I suspect it fetches the
entire table every time.

Gordon L. Burditt

[rallykarro]

Gordon Burditt skrev:

How do I at the best way perform select statements over multiple
databases?

If you're talking about different databases handled by the same
server, you can use the databasename.tablename to refer to tables
outside the current database.

If you're talking about different databases on different servers
potentially on different continents, MySQL has the "Federated" table
type, which allows you to reference a table on another MySQL server
as though it was on this one. Requires at least MySQL 5.0, as I
recall. Don't expect it to be fast. I suspect it fetches the
entire table every time.

Gordon L. Burditt

Hi,

yes, I may use db1.table1, db2.table approach since all my databases
are on the same node at the moment. Still, I want to transparantly be
able to add or remove databases from the set without changing the
specific sql statements refering to the table.

thanks,

karolina

[Gordon Burditt]

>> >How do I at the best way perform select statements over multiple

>databases?

If you're talking about different databases handled by the same
server, you can use the databasename.tablename to refer to tables
outside the current database.

If you're talking about different databases on different servers
potentially on different continents, MySQL has the "Federated" table
type, which allows you to reference a table on another MySQL server
as though it was on this one. Requires at least MySQL 5.0, as I
recall. Don't expect it to be fast. I suspect it fetches the
entire table every time.

Gordon L. Burditt

Hi,

yes, I may use db1.table1, db2.table approach since all my databases
are on the same node at the moment. Still, I want to transparantly be
able to add or remove databases from the set without changing the
specific sql statements refering to the table.

You have to tell it where the tables are. You are stuck with that.

Why are all the tables in different databases?

You might be able to refer to the table as $database_of_table1.table1
and let PHP substitute it, but there's got to be a file somewhere
you have to change if you move a table. You could also create a
federated table that points at the actual location of the table,
and re-create it if the actual table moves.

I suppose you could do something like selecting from
INFORMATION_SCHEMA.TABLES to figure out which databases have tables
named 'foo', and then try to figure out which one you should use.
(Requires MySQL 5.0 or greater, doesn't work across multiple servers,
and doesn't really solve the problem anyway).

I don't know of any way to *REMOVE* a table (vs. moving it elsewhere)
without affecting a SQL statement that uses it.

I don't know of any way to *ADD* a table to a SQL statement (presumably
a join) without affecting the SQL statement.

The best way I have found to keep databases out of the SQL is to
put all the tables in the same database, and use an include file
which defines the variables $mysql_server, $mysql_user, $mysql_password,
and $mysql_db, which I can change for the application without
changing the rest of the page. The application sets the current
database once and never changes it.

If for some reason I can't put all the tables in the same database
I usually end up using two connections (potentially on different
servers) with two sets of these. This approach does not scale very
well, and you can't do joins between tables on different connections.

Gordon L. Burditt