Performance of Querying Across Two MySQL Databases on The Same Server

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

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.

Two identical databases on same server with different running times

Following things could be the reason but for actual reasons you should look and profile your queries,

1. Though you think you are making simple inserts, its not a simple operation from DB perspective. (for every entry you insert following things may change and update

   1. Index
   2. Constraints
   3. Integrity of DB (PK-FK) and there are many things to consider.above things look simple but they take time if volume is high

2. Check volume of queries (if high no. of insert queries are getting executed then as might be knowing Insert is exclusive operation i.e. it locks the table for updating and volume is high that means more locking time and waiting time.)
   to avoid this probably you can try chaining or bulk operations
   Is bulk update faster than single update in db2?

3. Data Distribution also plays important role. if you are accessing heavily loaded tables then parsing/accessing/fetching data from such tables will also take time (it doesn't matter for single query but it really hurts for large volume of similar queries). Try to minimize that by tuning your queries.

MySQL: Many tables or many databases?

There should be no significant performance difference between multiple tables in a single database versus multiple tables in separate databases.

In MySQL, databases (standard SQL uses the term "schema" for this) serve chiefly as a namespace for tables. A database has only a few attributes, e.g. the default character set and collation. And that usage of GRANT makes it convenient to control access privileges per database, but that has nothing to do with performance.

You can access tables in any database from a single connection (provided they are managed by the same instance of MySQL Server). You just have to qualify the table name:

SELECT * FROM database17.accounts_table;

This is purely a syntactical difference. It should have no effect on performance.

Regarding storage, you can't organize tables into a file-per-database as @Chris speculates. With the MyISAM storage engine, you always have a file per table. With the InnoDB storage engine, you either have a single set of storage files that amalgamate all tables, or else you have a file per table (this is configured for the whole MySQL server, not per database). In either case, there's no performance advantage or disadvantage to creating the tables in a single database versus many databases.

There aren't many MySQL configuration parameters that work per database. Most parameters that affect server performance are server-wide in scope.

Regarding backups, you can specify a subset of tables as arguments to the mysqldump command. It may be more convenient to back up logical sets of tables per database, without having to name all the tables on the command-line. But it should make no difference to performance, only convenience for you as you enter the backup command.

performance effect of joining tables from different databases

From the performance point of view, there won't be ANY difference. Just keep your indexes in place and you will not notice whether you are using single DB or multiple DBs.

Apart from performance, there are 2 small implications that I can think of:
1. You can not have foreign keys across DBs.
2. Partitioning tables in DB based on their usage or based on applications can help you manage permissions in easy way.

Query across multiple databases on same server

It's not going to be the cleanest solution ever, but you could define a view on a "Master database" (if your individual databases are not going to stay constant) that includes the data from the individual databases, and allows you to execute queries on a single source.

For example...

CREATE VIEW vCombinedRecords AS
SELECT * FROM DB1.dbo.MyTable
UNION ALL
SELECT * FROM DB2.dbo.MyTable

Which allows you to do...

SELECT * FROM vCombinedRecords WHERE....

When your databases change, you just update the view definition to include the new tables.

Querying multiple databases at once

SELECT option_value
 FROM `database1`.`wp_options`
  WHERE option_name="active_plugins"
UNION
SELECT option_value
 FROM `database2`.`wp_options`
  WHERE option_name="active_plugins"

Query performance issue when executing from another database

It seems it's a compatibility level problem. Thanks to this post: https://dba.stackexchange.com/questions/194612/poor-performance-when-calling-query-from-another-db-on-same-server.

Curios it's that if compatibility level is 140 I have bad performance, but if I change the compatibility level to 100 everything works fine

---

Related Topics