Steps to make MySQL produce optimized results
» Identify the queries that execute slowly
» Watch for Opened Sql connections
» Carry benchmark test on Queries
» Execute queries with Explain statement to the performance of each queries
How to identify the queries that execute slowly?
Perform a bottom-down approach to identify the query or set of queries that make page download a time consuming task. If there are subqueries, attack it with a divide-and-conquer method to eliminate the queries that consumes memory. Better the queries are structured, better will the program execution be.
What is meant by MySQL benchmarking?
Benchmarking SQL queries means executing queries over and over to find the time it takes for the query to execute on average. MySQL has its own tool to do this testing. Other way to go ahead is to write a program that could execute for several execution cycle and comparing it results. An important point to note is to carry out the test in an environment that resembles your production server.
Visit http://dev.mysql.com/doc/refman/5.0/en/mysql-benchmarks.html to learn more about mysql benchmarking. Benchmarking tool comes free of cost along with mysql distribution.
Sample benchmarking query looks like the statement below
> Select benchmark(10000, “select * from users”);
