MySQL EXPLAIN

In SQL language, the EXPLAIN statement is used just before a SELECT and allows you to display the execution plan of an SQL query. This allows you to know how the Database Management System (DBMS) will execute the query and if it will use indexes and which ones.

Using this command the query will not return the results of the SELECT but rather an analysis of the query.
 

 

Syntax:

The syntax below shows an SQL query using the EXPLAIN command for MySQL:

EXPLAIN SELECT *
FROM `user`
ORDER BY `id` DESC

Reminder: in this example, the query will return information about the execution plan, but will not display the “real” results of the query.
 

Example :

To explain practically how the EXPLAIN statement works we will take a table of time zones in PHP. This table can be created from the following SQL query:

CREATE TABLE IF NOT EXISTS `timezones` (
 `timezone_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `timezone_groupe_en` varchar(50) DEFAULT NULL,
 `timezone_groupe_ar` varchar(50) DEFAULT NULL,
 `timezone_detail` varchar(100) DEFAULT NULL,
 PRIMARY KEY (`timezone_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=698;

The following query allows us to better understand the structure and the indexes of this table.

Let’s imagine that we want to count the number of time zones per group, for this, we can use the following SQL query:

SELECT timezone_groupe_en, COUNT(timezone_detail) AS total_timezone
FROM `timezones` 
GROUP BY timezone_groupe_en
ORDER BY timezone_groupe_en ASC

 

 

Analysis of the SQL query

In our example, we will see how MySQL will execute this query. To do this, we need to use the EXPLAIN statement:

EXPLAIN SELECT timezone_groupe_en, COUNT(timezone_detail) AS total_timezone
FROM `timezones` 
GROUP BY timezone_groupe_en
ORDER BY timezone_groupe_en ASC

The result of this SQL query is as follows:
 

 

 
In this example we see the following fields:

• id: SELECT identifier
• select_type: type of SELECT (example : SIMPLE, PRIMARY, UNION, DEPENDENT UNION, SUBQUERY, DEPENDENT SUBSELECT or DERIVED)
• table: table to which the row refers
• type: the type of join used (example : system, const, eq_ref, ref, ref_or_null, index_merge, unique_subquery, index_subquery, range, index or ALL)
• possible_keys: list of indexes that MySQL could use to speed up the execution of the query. In our example, no index is available to speed up the execution of the SQL query
• key: this column shows the indexes that MySQL has decided to use for the execution of the query
• key_len: indicates the size of the key that will be used. If there is no key, this column returns NULL
• ref: indicates which column (or constant) are used with the rows of the table
• rows: estimate of the number of rows that MySQL will have to examine to execute the query
• Extra: additional information about how MySQL will resolve the query. If this column returns results, it means that there are potentially indexes to use to optimize the performance of the SQL query. The “using temporary” message indicates that MySQL will have to create a temporary table to execute the query. The message “using filesort” indicates that MySQL will have to make another pass to return the rows in the right order

 

Adding an index:

It is possible to add an index on the column “timezone_groupe_ar” to the table which did not have one.

ALTER TABLE `timezones` ADD INDEX ( `timezone_groupe_ar` );

Adding this index will change the way MySQL can execute a SQL query. Performing the same query as before will give different results.
MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More