Not the answer you need?
Register and ask your own question!

Performance comparison between mutliple tables and multiple databases

hughbedohughbedo EntrantCurrent User Role Participant
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 Advisor
    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 Participant
    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.