Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Performance comparison between mutliple tables and multiple databases

hughbedohughbedo EntrantCurrent User Role Beginner
What are the performance implications of storing some data across 2 databases instead of duplicating this data in both databases? ie. is example 1 significantly slower than example 2:

Example 1:
SELECT Users.User.*, Transactions.Sales.date
FROM Users.User, Transactions.Sales
WHERE Users.User.id=Transactions.Sales.userid;

Example 2:
SELECT Users.User.*, Users.Sales.date
FROM Users.User, Users.Sales
WHERE Users.User.id=Users.Sales.userid;

Note in Example 2 the database Users would be significantly larger as it has data duplicated from the database Transactions.

Thanks for any help.

Comments

  • scoundrelscoundrel Contributor Inactive User Role Beginner
    Do you mean that you want to insert data in two DBs at the same time just to be able to use selects in one db? If yes, I'd not suggest to do so. AFAIK, there is no performance differences between one db queries and cross-db queries in MySQL at all.
  • hughbedohughbedo Entrant Current User Role Beginner
    Yes, that is basically what I am asking. To be honest I want to keep it as 2 databases and only have one copy of the data, not only because it is better from a data integrity standpoint, but it is less work for me. However the performance question was raised as a reason to have 2 copies of the data.

    If you are right that it is just as quick to run a query across 2 databases it is much better for me.

    Thanks for the help.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.