How to use MySQL Explain Statement

The database user needs to run different types of queries for different purposes. But sometimes many queries don’t return the expected results and the database administrator needs to find out the reason. MySQL contains a useful tool named EXPLAIN to diagnosis the error for which the query statement is not working properly. It is used at the beginning of any query statement to provide information about the execution of the query. EXPLAIN keyword can be used with SELECT, INSERT, UPDATE, DELETE and REPLACE statements. How to EXPLAIN keyword works with SELECT statements to diagnose errors of the query or optimize the query is shown in this article.

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.

Column	Description
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 DATABASE company;

USE company;

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,
order_date date,
customer_id INT(5) NOT NULL,
delivery_address VARCHAR(50) NOT NULL,
amount INT(11),
FOREIGN KEY (customer_id) REFERENCES customers(id))
ENGINE = INNODB;

INSERT INTO customers values
(NULL,'Johnathan', '18477366643' , 'john@gmail.com'),
(NULL,'Musfiqur Rahman', '17839394985','musfiq@gmail.com'),
(NULL,'Jimmy','14993774655' , 'jimmy@gmail.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.

SELECT * FROM customers;

Run the following statement to see the current record list of orders table.

SELECT * FROM orders;

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.

EXPLAIN SELECT * FROM customers \G;

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.

EXPLAIN SELECT customers.name, orders.order_date, orders.amount
FROM customers
JOIN orders ON (customers.id = orders.customer_id)
WHERE customers.name = 'Johnathan' \G

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.

SHOW WARNINGS \G

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.

EXPLAIN SELECT customers.name, orders.order_date, orders.amount
FROM customers
JOIN orders ON (customers.id = orders.customer_id)
WHERE orders.order_date = '10-10-2020' \G

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.

SHOW WARNINGS \G

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.

EXPLAIN SELECT id as ID
FROM customers
UNION ALL
SELECT customer_id as ID
FROM orders \G

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.

Conclusion:

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.

from Linux Hint https://ift.tt/34y5UPC