how to log mysql query that has long time to execute

To log slow-running MySQL queries, you can enable the Slow Query Log feature in MySQL. This log will record all queries that take longer than a specified amount of time to execute. Here's how to set it up:

Step 1: Enable Slow Query Logging

To enable slow query logging, you need to modify the MySQL configuration file (my.cnf or my.ini, depending on your system). Open the file and add or update the
On Ubuntu, this file is typically located at /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf.
following lines under the [mysqld] section or If the [mysqld] section is not found in your MySQL configuration file, you can add it manually:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2

Explanation:

• slow_query_log = 1 enables the slow query log.
• slow_query_log_file specifies the location of the log file (you can change the path to your preferred location).
• long_query_time = 2 sets the threshold for logging. Any query that takes longer than 2 seconds will be logged. You can adjust this value to meet your needs.

Step 2: Restart MySQL Service

After making the changes, restart your MySQL server to apply them:

sudo service mysql restart

Step 3: Verify the Settings

You can check if the slow query log is enabled and view its status using the following commands in the MySQL console:

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'slow_query_log_file';

Step 4: Analyzing the Slow Query Log

To analyze the slow queries, you can view the slow query log file with:

sudo cat /var/log/mysql/slow-query.log

Optional: Use the mysqldumpslow Tool

MySQL includes a utility called mysqldumpslow to summarize and analyze the slow query log:

mysqldumpslow -s r -t 10 /var/log/mysql/slow-query.log

• -s r sorts by the number of rows.
• -t 10 shows the top 10 slowest queries.

This setup will help you identify and optimize the queries that are taking too long to execute.