Using the Execution Plan (Explain)

This feature is available starting from version 10.2.0 and is supported only for MySQL.

Overview

When a user requests an SQL query through Workflow, they can view limited procedural information using the execution plan (Explain statement) provided by the DBMS. This feature is DBMS-dependent, so there may be differences when creating SQL Requests in Workflow depending on the DBMS of the connection.

What is an Execution Plan?

An execution plan is a set of operations created by the database optimizer to perform the most efficient query. It shows the execution steps and the cost at each step.

Applying SQL Request and Executing the Execution Plan (Explain)

The basic method for applying an SQL Request in QueryPie Workflow remains the same. (Refer to: SQL Request)

1. Select MySQL Connection and Database
   Choose the MySQL connection and database where the query will be executed, then input the query.

Only "Text" is supported. If "File" is selected, the execution plan (Explain) feature cannot be used.

2. Execution Plan (Explain) Options and Executing the Execution Plan
   Select the option at the bottom of the query editor and click the Explain button.

   1. Attach Explain Results : This switch determines whether the execution plan results will be attached to the SQL Request. (The default setting is ON.)

   2. Choose Display Format

      1. Table : The results are displayed in a table format.

      2. JSON : The results are displayed in JSON format.

• The queries eligible for the execution plan (those ending with a semicolon) are limited to 100.

• MySQL versions 5.6 and later can output the Explain results in JSON format. Therefore, Explain cannot output in JSON format for versions prior to 5.6.

3. Execution Plan (Explain) Results

   1. Table Format Results
      The results will be displayed as shown in the image below. For the meaning of each field, please refer to the MySQL reference documentation.
      

      

   2. Execution Plan Results in JSON Format
      When you select JSON and click the Explain button, the results will be displayed as shown in the image below. Click on {JSON} to view the entire JSON content.
      

      

      
      

      

      
      When exporting, a pop-up may appear asking for a password, depending on the administrator's settings.

Other Notes

1. Approver's Access to Explain Results
   The approver can view the attached Explain results in the Explain Results tab and use them as reference material for approval.

   

The approver can only view the Explain results in the format chosen by the requester when the Explain was performed. For example, if the requester outputs the Explain in JSON format and attaches it to the request, the approver can only view the results in JSON format and cannot change the display format to a table.

2. MySQL Syntax Supported for Explain Review
   MySQL supports different syntax for Explain analysis depending on the version.

The optimizer_trace and analyze are not supported in the execution plan (Explain) feature provided in SQL Request.