Skip to content
bobby_dreamer
GithubLinkedIn

mySQL - Finding slow queries using mySQLWorkBench

— mysql — 1 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