Hi Our site is being optimized right now cause the DB/sql is messed up The developers of the site are no longer available hence had to give the DB/sql opti. and on page opti to another vendor. Now the work is almost complete but db is still suffering there are queries taking lose to 3-7 seconds the vendor is also lost as he has done all the work but not seeing performance. Also while opti db he was not able to normalize db as not possible to explain DB as no documentation What can i do need to implement something today very urgent , will sql cache help or how would I normalize ( will that help) WHAT TO DO
I don't have much experience with MySQL but I do know MS SQL pretty well. Attempting to normalize an existing database can be extremely difficult and if the database contains a lot of data and lots of tables it would take some time to sort. It sound like your developers are doing the next best thing which is to attempt to optimize the on-page queries (the queries written into the page code) being used, but that will only be partly successful if the database is a mess. The next best thing to do is to transfer all your database queries into stored procedures and make use of views where tables are being joined - I believe MySQL 5 now supports both these. Stored Procedures will give your database a big performance kick, and the use of views will simplify these stored procedures. If that doesn't improve performance then you're left with a couple of choices... 1. Take the plunge and attempt to normalize the database. Don't expect it to be easy and if your DB data is constantly changing it will take a lot of time to get it all fixed and synched. It would be best to get all the work done in the background, get all the scripts ready, then take the DB off-line for a short period for the final transfer 2. Use more powerful hardware with fast disks (that faster the better for DB's) a decent CPU, and lots of RAM, but concentrate on the disks (speed not size) if funds are tight. I'm sure there are also caching solutions that will help, but as I said, I'm not that familiar with MySQL.
You should start off by identifying where the bottleneck is and using the EXPLAIN statement in MySQL. It will let you know what's going on. Without more information the problem could honestly be anywhere but 3-7s query is a long time. Things to consider: - Table is not indexed properly - Explain will let you know how it's accessing the data and what indexes are being used - Query is poorly designed, joins etc causing MySQL to push a lot of data to a tmp table for sorting/grouping etc. - Are you reading the data from RAM or is your database swapping to disk? Your DBA SHOULD NOT be lost as to why it's taking that long for a query, at the very least he should be able to see why even if he might not necessarily know how to fix it. www-mysqlperformanceblog-com/2006/07/24/extended-explain/.