We all know, it not good to keep queries which take long time to run and fetch result. But, one point also comes in mind what is the quick way to identify the Slow Running Query.
One of the good thing is MYSQL provide one way from which we can easily identify the slow running queries, and also store that slow running query in log.
“MYSQL doesn’t store SLOW running queries by default.”
To Enable Log for Slow Queries:
Edit my.cnf (configuration file) of mysql and uncomment these line under [mysqld] block:
long_query_time = 1 // in seconds log_slow_queries = /var/log/mysql/mysql-slow.log
The default long_query_time is 0, but we’ve set it to 1 here to make MySQL log all queries that take longer than 1 second to execute.
Feel free to change this value, as well as the location for the log file. You can then use the
mysqldumpslow command-line tool, included with MySQL, to get a summary of the slow-query-log file.
When you’re done, you need to restart the MySQL server for the changes in the my.cnf file to take effect
/etc/init.d/mysql restart OR sudo service mysql restart
And its done!
Enjoy Coding! 🙂