CODEWITHSUNDEEP
mysqldatabasedatabase-schemamultiple-databases
luqita
luqita

Reputation: 4077

Split MySQL databases

I have a question about split databases. This is the query in question:

SELECT COUNT(*) FROM monies.link_monies_new lcn '
         . 'INNER JOIN products.link l ON lcn.link_id = l.link_id '
         . 'INNER JOIN products.products_format af ON l.product_format_id = af.product_format_id '
         . 'INNER JOIN products.products_categories ac ON af.product_category_id = ac.product_category_id '
         . 'WHERE lcn.click_time BETWEEN FROM_UNIXTIME(1311721200) AND FROM_UNIXTIME(1311807600) '
         . 'AND ac.product_category_id = 1

The problem is that now the databases monies and products will be in different servers. What is the solution? Do I need two queries? I am a bit lost.

Thanks!

Upvotes: 3

Views: 1582

Answers (1)

Neville Kuyt
Neville Kuyt

Reputation: 29619

http://dev.mysql.com/doc/refman/5.0/en/federated-use.html

If I've understood your question properly, you want to have a query joining tables between 2 databases, and the databases will be located on separate servers.

Assuming you run the query on the "monies" server, you would create federated tables on the monies server, pointing at the "products" server. That way, your queries continue to work, and MySQL magically manages the fact they're on physically separate servers.

Performance tuning could be problematic, but it's the simplest way to keep your queries working as they are now.

For instance, you could create a local database on the "monies" server called "products"; within products, you would create a "link" table: 

CREATE TABLE link (
    link_id     INT(20) NOT NULL AUTO_INCREMENT,
    ....<<other columns from the link table>>
    PRIMARY KEY  (link_id)
)
ENGINE=FEDERATED

CONNECTION='mysql://fed_user@products_server:9306/products/link';

You could then run the query above without changing anything.

Upvotes: 4

Related Questions