How to check mysql slow queries or which queries are consuming cpu resources?

1. vi /etc/my.cnf and add following entries after mysqld.
[mysqld]
set-variable=long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log



2. Perform following steps:
mkdir /var/log/mysql
touch /var/log/mysql/log-slow-queries.log
chown mysql.mysql -R /var/log/mysql
3. Restart mysql. /etc/init.d/mysqld restart
4. Now you can use mysqlsla tool to check the slow queries. Here are the steps.
cd /usr/src
wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz
tar -xvzf mysqlsla-2.03.tar.gz
cd mysqlsla-2.03/bin
5. Execute following command :
./mysqlsla -lt slow --sort t_sum --top 5 /var/log/mysql/mysqld.slow

Now you'll able to see the results :) Contact developer to optimize the code.
optimize command is used to optimize the table. Here it is:
mysql>optimize table table_name;

check whether slow log has enabled or not.

mysql> SHOW VARIABLES like "%slow%"; //if not
mysql> SET @log_slow_queries=1;

Again restart mysql and use above tool after few minutes to check log.
By : singlehop.com