I have a database on mysql which is scaling on daily basis and right now its on 5M records and in a month it may double itself. I have a table containing users data which is the largest one. I have strong references implemented and a very normalized db design. Now when I run a report query (having joins to many tables), it takes too much time the first time and on repeating the same query it crashes. Following can be done: master slave replication indexes
in case of indexes if i alter that table with 5M+ records, it will be locked for a long time putting my application on hold. So its not an option.
In case of master slave i need your help. what should be my strategy to implement master slave. I am certain that the db would be 10M in one or two months and will keep on increasing. so i need a strategy to separate inserts from selects and do replication of my db or something. So.... Thanks in advance