Skip to main content
Skip table of contents

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.

image-20241029-234721.png
  1. 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.

image-20241029-235845.png
  • 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.

image-20241030-001252.png
  1. 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.

      image-20241030-002325.png

    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.

      image-20241030-003040.png

      Clicking {JSON} will allow you to view the entire content.


      image-20241030-003129.png

      You can copy and export the JSON results.


      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.

    image-20241030-004258.png

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.

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

Before MySQL 5.6.3

After MySQL 5.6.3

SELECT

SELECT , DELETE , INSERT , REPLACE , UPDATE

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

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.