EXPLAIN in SQL

Any SQL query is not fundamentally related to performance, but when used in large fields and its amount is expected to be very large, the performance issue is always present. To resolve these issues, the only possible solution is that the query must determine what it is doing and how much time it is consuming to do that task. And when any data analysts know about it, they may be able to optimize that query a lot. 

In SQL, the EXPLAIN keyword provides a description of how the SQL queries are executed by the databases. These descriptions include the optimizer logs, how tables are joined and in what order, etc.

Hence, it would be a useful tool in query optimization and knowing the details of its execution step by step. The EXPLAIN also indicates the fact that a user who doesn’t have any access to a particular database will not be provided details about how it executes the queries. So it maintains security as well.

The primary thing to note about EXPLAIN is that it will be used at the beginning of the query, i.e., before SELECT, INSERT, UPDATE, etc.

Syntax:

EXPLAIN (QUERY Statement) ; 

/* ONLY TAKES COMMAND AS PARAMETER */

Example:

Let’s first create a database known as GFG using the below command:

CREATE DATABASE GFG;

Now add tables to it.

SELECT * FROM gfgtable;

We added some data to it, and now it looks as below:

Now let’s use EXPLAIN to get an explanation of any query beginning from a simpler one.

 QUERY1:   EXPLAIN SELECT * FROM gfgtable;

All details of the execution is given under some specified column names.

Columns in the output of EXPLAIN Keyword:

Explain keyword results in their output using some column names as shown above. They are explained below:

• id: It represents the id of the query that is to be explained.
• SELECT_TYPE:  The complexity of the select clause is shown here. In the above case, it is very simple.
• table: The name of the table used is displayed here.
• Partitions: This shows the number of partitions in the table joined in the query.
• type: It specifies the join type.
• possible_keys: Which keys could have been used?
• key: which keys are used?
• key_len: Length of the key used.
• ref: Mentions any sort of reference used in the query while comparing columns or not.
• rows: The number of rows over which the query acts.
• Filtered: The rows that are filtered using the conditions in the WHERE clause.
• Extra: Some additional details regarding the executed query.

In this way, EXPLAIN keyword is used to get all the information about the query and tabulate them so that it can be stored in DB for further use.

QUERY 2:

 EXPLAIN
 SELECT CONCAT(g.FirstName,g.LastName) 
 AS FULLNAME
 FROM gfgtable AS g, gfgtable AS h
 WHERE g.Roll=h.Roll;

Output:

Here we can see how the type of join is also mentioned. SHOW WARNINGS are also used with the EXPLAIN keyword to display the details of the last executed line from where the error originated in the query.