measures and steps to be taken to perform database indexing

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.
D_wathiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nociSoftware EngineerCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
D_wathiAuthor 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 EngineerCommented:
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...
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

D_wathiAuthor 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 EngineerCommented:
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)...
D_wathiAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SugarCRM

From novice to tech pro — start learning today.