measures and steps to be taken to perform database indexing

D_wathi
D_wathi used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Engineer
Distinguished Expert 2018
Commented:
no difference from other indexing solutions:

1) determine your queries.
2) let the DB Engine explain  how the query is solved..,
3) the create indexes that would support the queries.
4) let the egine explain again to verify the index would be used.

Note: if you create an index i1 on table t1 using (fields f1, f2, f3)..
then you don't need an index i2 on table t1 using field f1, f2.

The index i1 would be used anyway.  During updates i1 & i2 would need to be maintained.
(This hold for most database engines). And definitely for MySQL.

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
Distinguished Expert 2018
Commented:
You can test the effects on a backup, but indexing commands need to be done on production as well.
In a backup there are never indexes stored, all indexes are created by the backup after data is restored.
Most database backups are SQL scripts btw.

I won't recommend experimenting on production servers...
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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
Distinguished Expert 2018
Commented:
No Need to do the full stack if you only need to test queries.......

You will need mysql on linux (obviously) best is in comparable configuration...
You will need to know the queries done from the sources.... and compare timing of those queires on inde

xed & non-indexed datbases.
You will need representative queries so you can measure effects.  same query again and again.  Those can be lifted from sources...
(Test in comparable situation, like   restart mysql service, then test one query without indexing, restart mysql test same query with indexing..._
etc.

You should probably start with queries that do joins as those will have the most impact.
Note that restructuring queries might yield more... (with joins, first selecting from the table that removes the most options is best).
Another one to check for are Slow-queries... (they go above a set threshold)...

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial