MySql Optimization and Good Programming Skills

Why we do optimization: A little time can save thousands in hardware, As you data grows, you’ll see that your application Performance will degrade over time and you’re probably not monitoring it anyway. It is easier than re-coding you applications.
So I am describing the ways to use for optimization of your database (MySql):
1. Benchmarking
2. Profiling
1. Benchmarking: - It make simple performance comparisons, Determine load limits, Test your application’s ability to deal with change. here are following benchmarking tools that are available:

• Mysql Benchmarking suite
  
• Mysql super smack
• MyBench
• ApacheBench ( It comes installed on almost any Unix/Linux distribution with the Apache web server installed, to use it you have to run following command like: > ab -n 100 -c 10 http://127.0.0.1/finduser1.php where 100 is the total no of connections and 10 the total concurrent requsts send at a time. )
  
• Httperf
  

Load generators: Contrieved Load Generators (Apache Bench) - For a particular web page, Realistic Load generators (Httperf) - For generating actual traffic.

2. Profiling: This enable you to procure information about - memory consumption, response times, locking, process counts from the engines that execute your SQL scripts and application code. There are a lot of profiling tools are available:

• The SHOW FULL PROCESSLIST and SHOW STATUS command ( SHOW FULL PROCESSLIST example: mysql> SHOW FULL PROCESSLIST; run this command and then see the result. SHOW STATUS Command Example:
  mysql> SHOW STATUS LIKE 'Qcache%';)
  
• The EXPLAIN command (Run Query with using EXPLAIN Command, it will give you the whole process of a query such as which column is used as index and how many rows are coming as a result and type of query.)
  
• SLOW QUERY and GENERAL QUERY LOG (This you can use by adding some lines in your mysql conf file (my.cnf): log-slow-queries = /var/log/mysql/mysql-slow.log long_query_time = 1 here /var/log/mysql/mysql-slow.log is the path of your log file by default this will create a file at /var/lib/mysql with a suffix of -slow.log and long_query_time is the time which is the longest time a query can take and its lowest value is 1 and DEFAULT value is 10)
  
• MyTop (It summarizes the SHOW FULL PROCESSLIST and various SHOW STATUS statements. Practicallu used it, its a good tool for profiling, but you need to install it from http://www.cpan.org/modules/by-module/Term/ )
  
• The Zend Advanced PHP Debugger extension (APD) - It is very good Profiling tool for profiling PHP as it does function call traces for your pages, memory consumption, execution time etc.
  

Practically, I have used AB (ApacheBench) for generating load for a single page of the site. It was giving the whole process time for a page when 10 (I gave 10) requests were concurrently processing for the page for total no of 100 connections, Then I checked my slow query log file which were having the list of all queries taking long time than given in mysql configuration file. I used mysqldumpslow (in command prompt go to the log file directory and do :> mysqldumpslow slowlogfilename) for checking this log file, mysqldumpslow will give how many times each query run and its process time. And I got that a lot of queries were repeated for a same row, So I changed my API in the form so that it will fetch the data from same table for same WHERE clause in a single database hit then I process those values further in my API. So I suggest that for a good programmer, he/she must design his/her API in such a way so that lowest data base hit will occur, and only select those column which are necessary at that time as use of SELECT * FROM... is much more costly than SELECT column_name1 FROM ...

I also used indexing for the tables in which SELECT queries were more called more that INSERT, UPDATE or DELETE. (I suggest to use index for a table for only that column which is called many time in WHERE clause of query and also make indexing for a single column when you are using AB so that you will get to know easily for which column index your query processing time is lower.)

I also suggest to INSERT or SELECT data in BUNDLE. (So that less data base hit will occur)