How to Read the MySQL Slow Query

How to Read the MySQL Slow Query 

MySQL has a slow query log that can be enabled if you want to identify the queries that are causing problems for a website or application. Checking slow queries is one of the most important steps in optimizing and tuning MySQL.

This article shows how to identify those 'slow queries' that need special attention and proper optimization.


First, let’s check on the server if slow query logging is enabled on MySql.

On Windows
cd c:\xampp\mysql\bin

bin>mysqladmin var | find "log_slow_queries"

On Linux 
bin>mysqladmin var |grep log_slow_queries
Enable the slow query log
MySQL prior to 5.1.0 requires a change to the MySQL my.cnf file and a restart in order to log slow queries. From MySQL 5.1.0 you can apparently change this dynamically without having to restart.

Too make the change permanent, you could add the following lines under the [mysqld] section of your my.ini or my.cnf configuration file:

 >vi /etc/my.cnf
 [mysqld]
 # Activate the Slow Query Log
 slow-query-log = 1
  
 # Log all queries taking more than 3 seconds
 long_query_time=5 # minimum: 1, default: 10

 # Write log to a custom filename
 slow-query-log-file=/var/log/mysql/log-slow-queries.log  #default: /data_dir/host_name-slow.log
if you do not specify the slow-query-log-file then the default value is host_name-slow.log.

If you specify the slow-query-log-file, then you must create the file manually and change owners:

 > mkdir /var/log/mysql
 > touch /var/log/mysql/log-slow-queries.log
 > chown mysql.mysql -R /var/log/mysql
Slow queries are written into the slow_log files and you can check it in a classic MySQL table of default mysql database.

Process the slow query log using the mysqldumpslow command to summarize the queries.


#To get top ten slow query in file /tmp/top_ten_slow_query.txt
bin>mysqldumpslow -t 10 /var/log/mysql/log-slow-queries.log > /tmp/top_ten_slow_query.txt


Also, Read

• Where is my MySQL database on the server?
• How to Redirect a Web Page