How to trace queries in MySQL

In this article, we will learn how MySQL trace queries. For this, we will rely on the OPTIMIZER_TRACE option.

This tracing option is available from MySQL 5.6 if you try to use it in an earlier version it will not work.

First, we will create an Orders table on which we will build the query we are going to trace. The table will have the following structure and content:

CREATE TABLE `orders` (   `id` int(11) NOT NULL,   `client_id` int(11) NOT NULL,   `received` datetime NOT NULL,   `processed` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,   `total` float NOT NULL,   `paid` float DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `orders` (`id`, `client_id`, `received`, `processed`, `total`, `paid`) VALUES (1, 1, '2019-05-19 13:18:13', '2019-05-20 09:11:57', 100, 75), (2, 1, '2019-05-19 13:21:18', '2019-05-20 15:32:14', 50.2, 20.5), (3, 2, '2019-05-20 14:19:18', '2019-05-20 21:27:30', 75, 60), (4, 2, '2019-05-20 15:47:15', '2019-05-21 08:23:28', 90.7, 20), (5, 3, '2019-05-21 08:23:15', '2019-05-21 10:33:24', 120, 100), (6, 1, '2019-01-22 05:20:08', '2019-02-25 12:10:19', 500, NULL), (7, 1, '2019-05-23 13:18:13', '2019-05-24 09:11:57', 150, 150), (8, 3, '2019-05-24 11:33:28', '2019-05-25 12:16:12', 200, 50), (9, 2, '2019-05-25 14:14:21', '2019-05-26 06:33:19', 250, 250), (10, 3, '2019-05-26 11:12:12', '2019-05-26 17:13:13', 300, 0); ALTER TABLE `orders` ADD PRIMARY KEY (`id`);

Script 1. Orders table creation

MySQL trace query

MySQL trace a query very easily with OPTIMIZER_TRACE. We only have to activate the option, execute the query, and then consult the content of the table INFORMATION_SCHEMA.OPTIMIZER_TRACE. When we no longer want to trace our queries, we deactivate the option.

We can execute the following query, to activate OPTIMIZER_TRACE option:

SET OPTIMIZER_TRACE="enabled=on";

Script 2. Activate OPTIMIZER_TRACE option

Then we execute the query we want to trace:

SELECT * FROM `orders`

Script 3. Query to retrieve Orders table rows

Result:

Table 1. Orders table content

Now we check the content of the table INFORMATION_SCHEMA.OPTIMIZER_TRACE:

SELECT QUERY, LEFT(TRACE,1000) AS TRACE FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

Script 4. Query to retrieve INFORMATION_SCHEMA.OPTIMIZER_TRACE  table content

Result:

Table 2. MySQL trace query

In the previous example, we limit the length of the TRACE column to 1000 characters. But if we want to see the complete content, we can execute the following script:

SELECT QUERY, TRACE AS TRACE FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

Script 5. Query to retrieve the complete TRACE column content

And finally, we can disable the option OPTIMIZER_TRACE with the following query:

SET optimizer_trace="enabled=off";

Script 6. Disable OPTIMIZER_TRACE option