Performance of querrying across two mysql databases on the same server?

Question

Is there any performance hit from querying over two (or more) databases on the same MySQL server, compared to if those databases had been merged into one?

Background, I have inherited maintenance over a web application that splits its data into three different databases that runs on the same server, one for content, one for users and group information and one for user generated data. This is convenient, for example it makes it easy to set up permissions, the user data is somewhat sensitive so people who don't need to know should not have access to it. However, one of the main features of the application is providing progress reports for users or groups on the content. Which means that it has to query across two or more of the databases in order to generate the report.

Is there any loss in performance doing it that way?

Answer 1

This would only introduce a noticeable problem if you were trying to use multiple select statements for the data in each database. It would be better to use some foreign keys, and then use a single join query to get a user's data and his associated user content. You can prefix schema names on your tables in the select join statement.

example would be similar to this:

SELECT a.user, b.user_content
    FROM database1.table1 AS a
    LEFT JOIN database2.table2 AS b
        ON a.user = b.user_id;

Answer 2

No. In MySQL, "databases" are essentially just catalogues which have no effect on the way data are stored or queried.

Querying two tables in different databases is the same as querying two tables in the same db, from a query execution standpoint.