EXPLAIN in SQL
Any SQL query is not basically related to performance, but when it comes to being used in big fields and its amount is to be very high, the issue regarding performance is always there. To cope up with these issues, the only way possible is that the query must itself tell what it is doing and how much time it is consuming to do that task. And when any data-analysts know about its working, then they may be able to optimize that query a lot.
In SQL, 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 which order etc.
Hence, it would be a beneficial tool in query optimization and knowing the details of its execution step by step. EXPLAIN also takes care of 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. Hence, it maintains security.
The main thing to note about EXPLAIN is that it will be used at the beginning of the query i.e before SELECT, INSERT, UPDATE, etc.
EXPLAIN (QUERY Statement) ; /* ONLY TAKES COMMAND AS PARAMETER */
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;
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 which is to be explained.
- SELECT_TYPE: The complexity in the select clause is showed 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 of 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 references used in query while comparing columns or not.
- rows: The number of rows over which query acts.
- Filtered: The rows which 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 they can be stored in DB for further references.
QUERY 2: EXPLAIN SELECT CONCAT(g.FirstName,g.LastName) AS FULLNAME FROM gfgtable AS g, gfgtable AS h WHERE g.Roll=h.Roll;
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.