How to Optimize MySql Database?
-
Hi Experts, We have created an application in ASP.Net 2.0 and with MySQL 5.0.67 as Backend. The application was running great from months, but recently we have discovered that there is a performance degrades in the database. Actually database was designed by one of my colleague how didn’t have much of database background so database design is not a professional one. I would also like to mention that this is my first Project on MySQL. I have worked on SQL Server till now. When I started analyzing the database I found indexing was missing in the tables. Introduction of Indexes has improved the performance somewhat. Can any one please tell me what else can I do to improve the performance. Please help me its urgent Thanks and Regards, Paramhans Dubey
-
Hi Experts, We have created an application in ASP.Net 2.0 and with MySQL 5.0.67 as Backend. The application was running great from months, but recently we have discovered that there is a performance degrades in the database. Actually database was designed by one of my colleague how didn’t have much of database background so database design is not a professional one. I would also like to mention that this is my first Project on MySQL. I have worked on SQL Server till now. When I started analyzing the database I found indexing was missing in the tables. Introduction of Indexes has improved the performance somewhat. Can any one please tell me what else can I do to improve the performance. Please help me its urgent Thanks and Regards, Paramhans Dubey
Paramhans Dubey wrote:
didn’t have much of database background so database design is not a professional one
So the design may be wrong to start with
Paramhans Dubey wrote:
indexing was missing in the tables
Another sign that he didn't know ehat he was doing
Paramhans Dubey wrote:
Please help me its urgent
Do what you should have done in the first place, employ someone who knows what they are doing. Without the schema, useage, etc its impossible to say what should be done anyway.
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP
-
Paramhans Dubey wrote:
didn’t have much of database background so database design is not a professional one
So the design may be wrong to start with
Paramhans Dubey wrote:
indexing was missing in the tables
Another sign that he didn't know ehat he was doing
Paramhans Dubey wrote:
Please help me its urgent
Do what you should have done in the first place, employ someone who knows what they are doing. Without the schema, useage, etc its impossible to say what should be done anyway.
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP
Hi Thanks for your reply and suggestion. We are in process to recruitin someone who knows the job, but meanwhile we just cant sit and wait for him to join and do the job. Anyways I was going through one article where I came to know about Table Engines and their usage. I also came to know there Using MyISAM table engine can improve the read perfomance of the tables but the author also wrote that
Your tables will get corrupted eventuallybut there was no explaination about how and why it happens? I fyou know the reason then kindly tell me Thanks and Regards, Paramhans Dubey. -
Hi Experts, We have created an application in ASP.Net 2.0 and with MySQL 5.0.67 as Backend. The application was running great from months, but recently we have discovered that there is a performance degrades in the database. Actually database was designed by one of my colleague how didn’t have much of database background so database design is not a professional one. I would also like to mention that this is my first Project on MySQL. I have worked on SQL Server till now. When I started analyzing the database I found indexing was missing in the tables. Introduction of Indexes has improved the performance somewhat. Can any one please tell me what else can I do to improve the performance. Please help me its urgent Thanks and Regards, Paramhans Dubey
OK, so your DB started performing poorly after it ran for awhile....that's a pretty broad problem, but here's a few things you might try: 1) I fully agree with Bob's post - if you want it to work correctly, get someone who knows what they're doing to set things up properly. (Though I understand that doesn't fix your problem today.) 2) Check for any tables that have grown excessively large. It's possible that you have some large logging / history tables and such that can easily be archived and deleted from the working tables. 3) If there's any way to narrow your problem down to a few areas (perhaps the top 2 or 3 stored procedures you're having trouble with?) you can look at modifying indexes specifically to help with those queries - just be sure you don't replace one problem with another. 4) You could always throw more hardware at it for a temporary fix... If you have any more specifics about what the problem is, perhaps we could better help. -Dave