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.
Similar Reads
SQL Engine The SQL Query Execution Engine is a critical component of any Database Management System (DBMS). It plays a key role in processing SQL queries and transforming them into meaningful results. Every time a user runs a query to fetch data from a database, various complex processes occur simultaneously t
7 min read
MySQL EXPLAIN ANALYZE MySQL's EXPLAIN ANALYZE command provides detailed information about query execution plans, including specifics about the optimizer's decisions, access methods, and estimated costs. Unlike the EXPLAIN statement, which outlines the execution plan without running the query, EXPLAIN ANALYZE actually exe
3 min read
How to Read MySQL EXPLAIN? Understanding how to interpret and analyze the output of the MySQL EXPLAIN statement is essential for database administrators, developers, and anyone involved in optimizing database performance. The EXPLAIN statement provides insights into how MySQL executes queries and helps identify potential bott
4 min read
Boolean Expressions in SQL Boolean expressions are a core concept in SQL, helping to filter and manipulate data based on conditions. These expressions evaluate to one of three Boolean values: TRUE, FALSE, or UNKNOWN. They are extensively used in WHERE clauses, HAVING clauses, and conditional statements to query and retrieve s
3 min read
MySQL Inner Join In MySQL, the INNER JOIN clause is used to combine rows from two or more tables based on a related column between them. The INNER JOIN returns rows when there is at least one match in both tables. If there are rows in the left table that do not have matches in the right table, those rows will not be
7 min read