EXPLAIN keyword output for SELECT query:
When the EXPLAIN keyword is executed with SELECT statement then the output of EXPLAIN will return the following columns.
|id||It indicates the identifier of the query. It represents the sequential number of SELECT queries.|
|select_type||It indicates the type of the SELECT query. The type can be SIMPLE, PRIMARY, SUBQUERY, UNION, etc.|
|table||It indicates the table name used in the query.|
|partitions||It indicates the partitions of the examined partitioned table.|
|type||It indicates the JOIN type or the access type of the tables.|
|possible_keys||It indicates the keys that can be used by MySQL to find rows from the table.|
|key||It indicates the index used by MySQL.|
|key_len||It indicates the length of the index that will use by the query optimizer.|
|ref||It indicates the columns or constants that are compared to the index named in the key column|
|rows||It indicates the lists of records that were examined.|
|filtered||It indicates the estimated percentage of table rows that will be filtered by the condition.|
|extra||It indicates the additional information regarding the query execution plan.|
Suppose you have two related tables named customers and orders in the database named company. The necessary SQL statements for creating the database and the tables with data are given below.
CREATE TABLE customers (
id INT(5) AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
mobile_no VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL) ENGINE = INNODB;
CREATE TABLE orders (
id VARCHAR(20) PRIMARY KEY,
customer_id INT(5) NOT NULL,
delivery_address VARCHAR(50) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id))
ENGINE = INNODB;
INSERT INTO customers values
(NULL,'Johnathan', '18477366643' , 'email@example.com'),
(NULL,'Musfiqur Rahman', '17839394985','firstname.lastname@example.org'),
(NULL,'Jimmy','14993774655' , 'email@example.com');
INSERT INTO orders value
('1937747', '2020-01-02', 1, 'New Work', 1000),
('8633664', '2020-02-12', 3, 'Texas', 1500),
('4562777', '2020-02-05', 1, 'California', 800),
('3434959', '2020-03-01' , 2 , 'New Work', 900),
('7887775', '2020-03-17', 3 , 'Texas', 400);
Run the following statement to see the current record list of customers table.
Run the following statement to see the current record list of orders table.
Use of simple EXPLAIN statement:
The following SQL statement will return the keyword of the EXPLAIN statement of a simple SELECT query that retrieves all records from customers table.
The following output will appear after running the statement. It is a single table query and no special clauses like JOIN, UNION, etc. are used in the query. For this, the value of select_type is SIMPLE. The customers table contains only three records, that’s why the value of rows is 3. The value of filtered is 100% because all records of the table is retrieved.
Use of EXPLAIN in SELECT query with JOIN:
The following EXPLAIN statement is applied in a SELECT query of two tables with the JOIN clause and a WHERE condition.
The following output will appear after running the statement. Here, select_type is SIMPLE for both tables. Two tables are related by one-to-many relationship. The primary key of customers table is used as a foreign key of orders table. For this, the value of possible_keys for the second row is customer_id. The filtered value is 33% for customers table because ‘Johnathan’ is the first entry of this table and no need to search more. The filtered value of orders table is 100% because of all values of orders table required to check to retrieve the data.
There is a warning in the output of the above statement. The following statement is used to see the query that is executed after making any change by the Query Optimizer or check the reason for the error if any error occurs after executing the query.
There is no error in the query. The output shows the modified query that is executed.
Use of EXPLAIN to find out the error of SELECT query:
The SELECT query that is used in the following EXPLAIN statement contains an error. The date format that is supported by MySQL is ‘YYYY-MM-DD’. But in the WHERE condition of this query, the date value is given as ‘DD-MM-YYYY’ that is wrong.
The following output will be appeared after running the statement. It will show two warnings. One is the default that is explained in the previous example and another is for the date error mentioned before.
Run the statement to see the error.
The output shows the error clearly with an error message and column name.
Use of EXPLAIN in SELECT query with UNION ALL operator:
UNION ALL operator is used in the SELECT query to retrieve all matching column values with duplicates from the related tables. The following statement will display the EXPLAIN output of applying UNION ALL operator between customers and orders tables.
The following output will appear after running the statement. Here, the value of select_type is UNION for the second row of the output and the value of Extra is the index.
Very simple uses of the EXPLAIN statement are shown in this article. But this statement can be used to solve various complicated database problems and optimize the database for better performance.