mySQL - Finding slow queries using mySQLWorkBench

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

mysql> SHOW VARIABLES LIKE '%query%';
+------------------------------+------------------------+
| Variable_name                | Value                  |
+------------------------------+------------------------+
| binlog_rows_query_log_events | OFF                    |
| ft_query_expansion_limit     | 20                     |
| have_query_cache             | NO                     |
| long_query_time              | 10.000000              |
| query_alloc_block_size       | 8192                   |
| query_prealloc_size          | 8192                   |
| slow_query_log               | ON                     |
| slow_query_log_file          | SUSHANTH-VAIO-slow.log |
+------------------------------+------------------------+
8 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

mysql> SHOW VARIABLES LIKE '%min_exam%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| min_examined_row_limit | 0     |
+------------------------+-------+
1 row in set (0.01 sec)

Once everything is set

mySQLWorkBench

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

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

Thanks