MySQL - EXPLAIN

Table of content

The MySQL EXPLAIN Statement

The MySQL EXPLAIN statement is used to provide the execution plan of a query. This statement works similar to the DESCRIBE query; while the DESCRIBE query provides the structure plan of a table, the EXPLAIN statement describes how a query is being executed.

You can use the EXPLAIN statement in situations where a query is taking too much time in order to be executed. It displays the execution plan of such slower queries, allowing you to apply indexes wherever necessary to speed up the execution process.

Note that you cannot use too many indexes on a query either; as it might make the query even slower.

This statement works with the SELECT, DELETE, INSERT, REPLACE and UPDATE statements.

Syntax

Following is the syntax of the EXPLAIN statement −

EXPLAIN tbl_name [col_name | wild]

Example

Assume we have created a table named CUSTOMERS in MySQL database as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   ADDRESS CHAR (25),
   PRIMARY KEY (ID)
);

You can use the EXPLAIN statement to view the execution plan of this table as shown below −

EXPLAIN CUSTOMERS;

The output will provide information about the table's structure, including columns and their attributes as follows −

Field Type Null Key Default Extra
ID int NO PRI NULL
NAME varchar(20) NO NULL
ADDRESS char(25) YES NULL

You can also use the EXPLAIN statement to obtain details about a specific column as shown below −

EXPLAIN CUSTOMERS NAME;

Output

Following is the output obtained −

Field Type Null Key Default Extra
NAME varchar(20) NO NULL

EXPLAIN is most commonly used with SELECT queries to analyze their execution plans. Consider the following query −

EXPLAIN SELECT * FROM CUSTOMERS WHERE NAME LIKE 'k%';

The table obtained is as follows −

id select_type table partitions type possible_keys
1 SIMPLE CUSTOMERS NULL ALL NULL

Note that not all columns in the table have been displayed in the output above; there are additional columns present.

EXPLAIN and ANALYZE

If we use the EXPLAIN statement with ANALYZE, it gives additional information such as timing of the execution and iterator-based information like −

  • Estimated execution cost.
  • Estimated number of returned rows.
  • Time to return first row.
  • Time to return all rows (actual cost), in milliseconds.
  • Number of rows returned by the iterator.
  • Number of loops.

Example

Following is an example of the EXPLAIN statement with ANALYZE −

EXPLAIN ANALYZE SELECT * FROM CUSTOMERS;

It displays the output that includes more timing and cost-related details as shown below −

EXPLAIN
–> Table scan on CUSTOMERS (cost=0.35 rows=1) (actual time=0.070..0.070 rows=0 loops=1)

Example

First, let us insert values into the CUSTOMERS table created above using the INSERT statement −

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 'Ahmedabad' ),
(2, 'Khilan', 'Delhi' ),
(3, 'kaushik', 'Kota'),
(4, 'Chaitali', 'Mumbai' ),
(5, 'Hardik', 'Bhopal' ),
(6, 'Komal', 'MP' ),
(7, 'Muffy', 'Indore' );

Let us create another table ORDERS, containing the details of orders made and the date they are made on −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUST_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Now, we are inserting some data into the ORDERS table as follows −

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

Following query deletes records from the above created tables −

SELECT * FROM CUSTOMERS 
INNER JOIN ORDERS ON ORDERS.CUST_ID = CUSTOMERS.ID;

We get the following output −

ID NAME ADDRESS OID DATE CUST_ID AMOUNT
3 Kaushik Kota 102 2009-10-08 00:00:00 3 3000.00
3 Kaushik Kota 100 2009-10-08 00:00:00 3 1500.00
2 Khilan Delhi 101 2009-11-20 00:00:00 2 1560.00
4 Chaitali Mumbai 103 2008-05-20 00:00:00 4 2060.00

To obtain information about this query's execution, you can use the EXPLAIN ANALYZE statement as follows−

EXPLAIN ANALYZE SELECT * FROM CUSTOMERS 
INNER JOIN ORDERS ON ORDERS.CUST_ID = CUSTOMERS.ID\G;

The result produced is as follows −

*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join  (cost=2.05 rows=4) (actual time=0.117..0.145 rows=4 loops=1)
    -> Table scan on ORDERS  (cost=0.65 rows=4) (actual time=0.078..0.095 rows=4 loops=1)
    -> Single-row index lookup on CUSTOMERS using PRIMARY (ID=orders.CUST_ID)  (cost=0.28 rows=1) (actual time=0.010..0.010 rows=1 loops=4)

1 row in set (0.00 sec)

The explain_type Option

You can also specify the format in which you want to retrieve the information using the explain_type option. It allows you to choose between TRADITIONAL, JSON, and TREE formats. These different formats provide the same information but in a more structured manner for your analysis.

Example

In here, we are retrieving the information in TREE format using the explain_type option −

EXPLAIN ANALYZE FORMAT = TREE SELECT * FROM CUSTOMERS 
INNER JOIN ORDERS ON ORDERS.CUST_ID = CUSTOMERS.ID;

Following is the output obtained −

-> Nested loop inner join  (cost=2.05 rows=4) (actual time=0.111..0.136 rows=4 loops=1)
    -> Table scan on ORDERS  (cost=0.65 rows=4) (actual time=0.073..0.089 rows=4 loops=1)       
    -> Single-row index lookup on CUSTOMERS using PRIMARY (ID=orders.CUST_ID)

Now, we are retrieving information in JSON format −

EXPLAIN FORMAT = JSON SELECT * FROM CUSTOMERS;

After executing the above code, we get the following output −

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.95"
    },
    "table": {
      "table_name": "CUSTOMERS",
      "access_type": "ALL",
      "rows_examined_per_scan": 7,
      "rows_produced_per_join": 7,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.25",
        "eval_cost": "0.70",
        "prefix_cost": "0.95",
        "data_read_per_join": "1K"
      },
      "used_columns": [
        "ID",
        "NAME",
        "ADDRESS"
      ]
    }
  }
}
Advertisements