I'm in a dilemma if creating redundant database will speed up the access considering only the CRUD factor.
Is it effective to create a dedicated DB for creating/updating/deleting records
and a separate DB for viewing only?
Currently using MySQL.
Mysql – how effective creating 2 database with same content
MySQL
Related Question
- Mysql – Creating a database with only MySQL command line
- MySQL – How to Batch Multiple Insert Statements for Database Dump
- Mysql – How to install two thesql instance on same machine
- Mysql – Is there benefit from having 2 tables for 1 entity
- Mysql – AWS RDS : Setting Create and other grants only for single Database is not working
- MySQL – Best Practices for Exposing Record IDs to API Users
- Mysql – Options for archiving old MySQL 5.7data
Best Answer
This is called Write masters with Read slaves. Check out MySQL replication. You'll find an interesting document here (check out the slides) - but you probably won't be needing the level of availability of Booking.com, but hey, who knows? :-).
There are many different replication/HA topologies, but this will give you a start. Also, see here (Andrew Morgan is the author of MySQL Cluster).