I have a database that contains a large number of tables that can be divided into multiple databases. The connection between the tables is, for example:

DB1: users (contains the field 'client_id') DB2: customers (contains all the tables and relationships)

The two DBs are therefore connected via the 'client_id' field in the users table in DB1, and the 'id' field in the customers table in DB2.

Additionally, I also have a third DB that is connected in a similar way to the second DB.

Is this good practice? I have read that it can create performance problems, but keeping everything in a single DB doesn't seem ideal either.

Do you have any ideas or suggestions? Can this approach work?

CodePudding user response：

In MySQL, databases (aka schemas) are just subdirectories under the datadir. Tables in different schemas on the same MySQL Server instance share the same resources (storage, RAM, and CPU). You can make relationships between tables in different schemas.

Assuming they are on the same MySQL Server instance, there is no performance implication with keeping tables together in one schema versus separating them into multiple schemas.

Using schemas is mostly personal preference. It can make certain tasks more convenient, such as granting privileges, backing up and restoring, or using replication filters. These have no direct effect on query performance.