Debugging MySQL Queries

If you ever work with MySQL databases before, is very probable that sometime was necessary try to discover/understand why or which queries are slow.

Searching through the web I’ve found a very interesting post talking about this problem.

Well, to be honest this approach is available on MySQL documentation. However, I put here a brief explanation and link to this article.

This approach is very simple, is a set command where you can define a table or file to save all your MySQL Queries.

This is well documented in this article “How to show the queries log in MySQL?” from TablePlus blog. By the way, I really like this Database Manager and I recommend to use it too.

---

1. First, check if you already have the two tables slow_log and general_log existing in the mysql database.

By default, in my database I already had this. If not, you have to create them, note that you have to create in the mysql database.

CREATE TABLE `general_log` (
   `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
                          ON UPDATE CURRENT_TIMESTAMP,
   `user_host` mediumtext NOT NULL,
   `thread_id` bigint(21) unsigned NOT NULL,
   `server_id` int(10) unsigned NOT NULL,
   `command_type` varchar(64) NOT NULL,
   `argument` mediumtext NOT NULL
  ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'


CREATE TABLE `slow_log` (
   `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 
                          ON UPDATE CURRENT_TIMESTAMP,
   `user_host` mediumtext NOT NULL,
   `query_time` time NOT NULL,
   `lock_time` time NOT NULL,
   `rows_sent` int(11) NOT NULL,
   `rows_examined` int(11) NOT NULL,
   `db` varchar(512) NOT NULL,
   `last_insert_id` int(11) NOT NULL,
   `insert_id` int(11) NOT NULL,
   `server_id` int(10) unsigned NOT NULL,
   `sql_text` mediumtext NOT NULL,
   `thread_id` bigint(21) unsigned NOT NULL
  ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'

The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.

The slow query log consists of SQL statements that take more than long_query_time seconds to execute and require at leastmin_examined_row_limit rows to be examined. The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization.

The minimum and default values of long_query_time are 0 and 10, respectively. The value can be specified to a resolution of microseconds.

From TablePlus article

2. Enable Query logging on the database

SET global general_log = 1; 
SET global log_output = 'table';

-- You can also disable query log on the database
SET global general_log = 0; 

3. Consulting queries

SELECT * FROM mysql.general_log;

---

What else?

What they don’t tell in this first article is that you can use this same approach but writing to a file.

To do this you need to change to:

SET global log_output = 'file';

To configure the file that will be used you need to define the general_log_file var on MySQL System variables.

For MySQL < 5.1.29

Change file /etc/my.cnf and inside [mysqld] section put.

log   = /path/to/query.log  #works for mysql < 5.1.29

For MySQL 5.1.29+

general_log_file = /path/to/query.log
general_log      = 1

---

Other useful link to documentation https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

I hope that you liked this little tip.