Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Jun 8 '13 at 7:23

This question came from our site for professional and enthusiast programmers.

    
Can you show us a sample query and the tables involved (with SHOW CREATE TABLE ...? With only 5M rows, are you sure you have the tables properly indexed? –  ypercube Jun 8 '13 at 8:25
    
Fixing your table now will be twice as easy as fixing it when it's twice as big. –  Alain Collins Jun 16 '13 at 5:06
    
Right now i cannot add an index on the table because it will take around 2 or more hours to apply the change to the table and during that time the table will get locked. I don't have a sample query cuz its in django and django queries are not queries, they are functions actually. so what should i do in this situation –  Rai Muhammad Ehtisham Jun 17 '13 at 6:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.