A query is considered as slow if it takes more than “long_query_time” seconds to execute. By default, the value of this variable is set to “1″.
1). Identifying MySQL slow queries
************************************
First let’s check on the mysql server if we have slow query logging enabled:
mysqladmin var |grep log_slow_queries | log_slow_queries | OFF
2) . Activate the logging of mysql slow querie
************************************************
MySQL allows to log slow running queries to a file. To enable slow query logging, just add the following lines to /etc/my.cnf, and restart mysql.
log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
This configuration will log all queries that take more than 1 sec in the file /var/log/mysql/mysql-slow.log.
Another alternative for enabling this feature is to restart mysql with the –log-slow-queries[=file_name] option.
2. Investigate the mysql slow queries log
*******************************************
MySQL gives us a small tool that does exactly this: mysqldumpslow
For example using:
mysqldumpslow -s c -t 10
We get the top 10 queries (-t 10) sorted by the number of occurrences in the log (-s c).
ref : http://praji.wordpress.com/slow-query-logging-in-mysql/