Skip to content

bobby_dreamer

mySQL - Finding slow queries using mySQLWorkBench

mysql1 min read

Easy way to find which query is consuming lots of time in mySQL using default options and tools.

First check if the required options are enabled
1mysql> SHOW VARIABLES LIKE '%query%';
2+------------------------------+------------------------+
3| Variable_name | Value |
4+------------------------------+------------------------+
5| binlog_rows_query_log_events | OFF |
6| ft_query_expansion_limit | 20 |
7| have_query_cache | NO |
8| long_query_time | 10.000000 |
9| query_alloc_block_size | 8192 |
10| query_prealloc_size | 8192 |
11| slow_query_log | ON |
12| slow_query_log_file | SUSHANTH-VAIO-slow.log |
13+------------------------------+------------------------+
148 rows in set (0.01 sec)

Row we are checking are slow_query_log & long_query_time

  • long_query_time -- consists of SQL statements that take more than long_query_time seconds to execute and require at least min_examined_row_limit rows to be examined
1mysql> SHOW VARIABLES LIKE '%min_exam%';
2+------------------------+-------+
3| Variable_name | Value |
4+------------------------+-------+
5| min_examined_row_limit | 0 |
6+------------------------+-------+
71 row in set (0.01 sec)

Once everything is set

mySQLWorkBench
  • Open mySQLWorkBench
  • Click Server Logs
  • On the right side, select Slow Log File

mySQLWorkBench

You can right click on the log record and find details and partial query.

Thanks