I've got a question about split databases. This is actually the query under consideration:

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

However , the databases money and items come in different servers. What's the solution? Will I need two queries? I'm a little lost.

Thanks!

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

If I have understood your question correctly, you need to possess a query joining tables between 2 databases, and also the databases is going to be situated on separate servers.

Presuming you take the query around the "money" server, you'd create federated tables around the money server, pointing in the "items" server. This way, your queries still work, and MySQL like magic handles the very fact they are on physically separate servers.

Performance tuning might be problematic, but it is the easiest way and also hardwearing . queries working because they are now.

For example, you can produce a local database around the "money" server known as "items" within items, you'd produce 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 can then run the query above without altering anything.