We help IT Professionals succeed at work.

measures and steps to be taken to perform database indexing

274 Views
Last Modified: 2019-01-23
Dear Experts
We are having web based CRM application running on LAMP stack web server and data base servers are running two different server. Our software developers have decided to create indexes based on their analysis due to slow queries observations, the database server is mysql 5.4 version.
As it is production server the indexing will affect the work and also the tables size is huge and overall database size is 65GB. Please suggest the best practice, following is the approach can think about but again not sure if this is best recommended but there is a challenge as mentioned, please suggest on how such cases are handled.
1.Setup new webserver and mysql server with same config and same versions and take backup of production server database and import it to the new database server and create indexes and test, this process may take few days to week and by the time indexing gets completed which may take few days during this period production server database would have undergone changes as users are working on production instance, can you please suggest the best way to handle this.
Comment
Watch Question

Software Engineer
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
thank you very much for the inputs, can we do this directly on production server because already users are experiencing slowness, take the complete backup do indexing in the development server and then import  to production if this is done then during this indexing on development server production server some part of data would have changed as users will be working on it , how to handle this situation please help to understand this and recommend steps.
nociSoftware Engineer
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
thanks for the input, please correct me if iam wrong in the understanding on following
1. setup new server LAMP stack (testing)bring entire database of production to here
2. do indexing here and observe the effects
3.go back the production and do the same indexing based on observed facts testing server.
please suggest me is this correct or help me with correct steps please.
nociSoftware Engineer
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
please help me understand the representative queries that you have mentioned.
2. perform indexing on test server observe and then do the same in production is this correct please suggest.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions