Using Execution Plan (Explain) Feature

This feature was added from version 10.2.0. (As of 10.2.0, only MySQL is supported.) From version 11.2.0, administrators can disable this feature. If users cannot use this feature, it means the administrator has disabled it through policy.

Overview

When users request SQL Request through Workflow, they can check limited procedural information about the query using the execution plan (Explain statement) provided by the DBMS. Since this feature is dependent on the DBMS, there may be differences for each DBMS corresponding to the connection when writing SQL Request in Workflow.

What is an execution plan?
It is a series of work sets or work procedures generated by the database optimizer to perform the most optimal query. You can see the execution steps step by step and the cost for each step.

Writing SQL Request and Performing Execution Plan (Explain)

The basic method of writing SQL Request in QueryPie Workflow is the same. (Reference: Requesting SQL Request)

MySQL Connection and Database Selection
Select the MySQL Connection and Database to execute the query and enter the query.

Content Type only supports “Text”. If you select “File”, you cannot use the execution plan (Explain) feature.

Execution Plan (Explain) Options and Execution Plan Execution
Select the options at the bottom of the query editor and click the Explain button.
1. Attach Explain Results: This is a switch to select whether to attach the execution plan results to the SQL Request. (The default value is OFF. If necessary, turn the switch ON to attach the execution plan results.)
2. Select result display format
  1. Table: Results are displayed in table format.
  2. JSON: Results are displayed in JSON format.

The number of queries (statements ending with semicolons) that can be targeted for execution plan is limited to 100.
MySQL can output Explain results in JSON format from version 5.6 onwards. Therefore, if you perform Explain on versions before 5.6, you cannot output in JSON format.

Checking Execution Plan (Explain) Results
1. Checking Table Format Results
  Results are displayed as shown in the figure below. Please refer to the MySQL reference documentation for the meaning of each field.
2. Checking JSON Format Results
  If you select JSON and click the Explain button, results are displayed as shown in the figure below. Click {JSON} to view the entire JSON content.
  
  When exporting, a popup requesting password input may appear depending on administrator settings.

Other Notes

Approver’s Execution Plan (Explain) Result Check
Approvers can check the attached execution plan (Explain) results in the Explain Results tab and use them as reference materials for approval.

The execution plan (Explain) results that approvers can see are only available in the format selected when the submitter performed the execution plan. That is, if the submitter performed the execution plan in JSON format and attached it for submission, the approver can only see the JSON format results and cannot change the output format to table.

Statements that can be reviewed through execution plan (Explain) in MySQL
MySQL differs in the supported statements for analysis through execution plan by version.

MySQL 5.6.3 and earlier	MySQL 5.6.3 and later
SELECT	SELECT , DELETE , INSERT , REPLACE , UPDATE

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