0

I have app that uses DB with 50+ tables. Now i'm in situation that i need to install another instance of app. Problem is that i would like to use some tables as "common" data, e.g. "brands" or "cities" or "countries" in both (for now there are only 2, but it might soon be more) apps.

I searched and find that i can make "common" DB with such tables, and have views in each DB instance that points to corresponding table.

Main app queries are heavily relying on that common tables, so i'm concerned if that will slow down my queries since views don't have indexes?

Are there some better practices? I'm looking now for replication in mysql manual. Is that way to go? Replicate tables from common DB to app instance DB's? Can this be one-direction replication? (only tables in "common" DB can be changed and then replicated to other DB's)?

thanx for advice Y

Improve this question
1
  • Views don't have indexes, but the underlying tables should have them, and they'll be used when necessary. On the other hand, in mySQL, with appropriate privileges, one user can access several databases at once in the same server, not just views. So, if you need to, you can access the tables themselves. In other database systems, you do the same with schemas.
    – joanolo
    Aug 6, 2017 at 17:02

1 Answer 1

Reset to default
2

If the "common" tables on the secondary "app instances" are being accessed read only, then your easiest option will be doing a traditional master slave(s) configuration with a replicate_ignore_table = ... line put into all slaves for all tables except the "common" ones.

If you need to have identical behavior between all app instances (meaning you're not using app logic to isolate writes to the master only), then I would seriously considering using a galera cluster product of some sort.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.