optimizing a MySQL database

broderick710's Avatar, Join Date: Sep 2011
Newbie Member
What are the steps to optimize MySQL database?
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
Why do you want to know this from C-C++ Experts?
xpi0t0s's Avatar, Join Date: Aug 2004
Same steps as any other kind of optimisation. First establish where the problems are, typically via some kind of profiling. Secondly define specific measurable targets for performance which should be derived from the project requirements; "as fast as possible" isn't a good target because this is not clearly defined, for example you could probably make it "as fast as possible" by installing it onto 20,000,000 weather computers, but if your budget doesn't stretch that far then that's not actually possible. A good target could be something like "Needs to process a query in 1 second or less because the application is required to process 3600 queries per hour" - then if the lowest achievable target is 5 seconds then you know you need to setup 5-way parallel query processing.

There's a lot to it and there's probably a manual on MySQL optimisation. There's certainly one for Oracle; it's the "Performance Tuning Guide" for 11gR2, so maybe you could read that for some inspiration. The principles will all apply even if the commands given don't work on MySQL.