Syntax:
The EXPLAIN statement is applied on any MySQL query. The syntax of the EXPLAIN statement is given below.
Limitations of EXPLAIN Statement:
Some limitations exist for using EXPLAIN statements in MySQL, mentioned below.
- It can’t be applied to any stored procedure.
- It doesn’t provide any information about query optimization.
- It doesn’t provide any information about the output of stored functions and triggers.
- The statistical information provided by the EXPLAIN statement may not be appropriate.
Pre-requisites:
You have to create one or more tables with data in a MySQL database to check the way of using the EXPLAIN and EXPLAIN ANALYZE statements in MySQL. Open the terminal and connect with the MySQL server by executing the following command.
Run the following command to create a database named test_db.
Run the following command to select the database.
Run the following query to create a table named teachers with four fields.
id INT NOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
dept_name VARCHAR(15),
designation VARCHAR(30));
Run the following INSERT query to insert three values into the teachers table.
('9045', 'Zarin Chowdhury', 'English', 'Lecturer'),
('2356', 'Mahmudul Hasan', 'BBA', 'Lecturer');
Run the following query to create a table named courses with four fields that contains a foreign key that will create the one-to-many relationship from the teachers table to the courses table.
id VARCHAR(10) NOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
credit FLOAT(3,2),
teacher_id int,
CONSTRAINT fk_teacher FOREIGN KEY (teacher_Id)
REFERENCES teachers(id)
ON DELETE CASCADE ON UPDATE CASCADE );
Run the following INSERT query to insert four records into the courses table.
('CSE-303', 'Artificial Intelligence', '3.0', '7854'),
('BBA-101', 'Marketing', '2.0', '2356'),
('ENG-201', 'Literature', '3.0', '9045'),
('CSE-404', 'Multimedia', '3.0', '7854');
Uses of EXPLAIN and EXPLAIN ANALYZE keyword:
This part of the tutorial shows the uses of EXPLAIN and EXPLAIN ANALYZE statements on the SELECT query.
Example-1: Using EXPLAIN statement for single table
The way of using EXPLAIN keyword for the SELECT query of reading a single table has been shown here. Run the following SELECT query to read all records of the teachers table.
The following output will appear after executing the above query.
Run the following SELECT query with EXPLAIN statement to Check the execution information of the previously executed SELECT query.
The following output will appear after executing the above query. If you want to know more details about the output fields of the EXPLAIN statement, then you can check the tutorial.
Run the following SELECT query to read the records of the courses table based on the condition of the WHERE clause. The query will print the records from the courses table where the value of the credit field is 3.0.
The following output will appear after executing the above query.
Now, run the following SELECT query with EXPLAIN statement to Check the execution information of the previously executed SELECT query.
The following output will appear after executing the above query. The output shows the execution details of the query with where clause such as the value of the rows field is 4, the value of the Extra field is ‘Using where,’ etc.
Example-2: Using EXPLAIN for multiple tables
The way of using EXPLAIN keyword for the SELECT query of reading records from two related tables has been shown here. Run the following SELECT query to read the course code, course name, and the teacher’s name from the teachers and courses tables.
FROM teachers, courses
WHERE courses.teacher_id = teachers.id;
The following output will appear after executing the above query.
Now, run the following SELECT query with EXPLAIN statement to Check the execution information of the previously executed SELECT query.
FROM teachers, courses
WHERE courses.teacher_id = teachers.id;
Example-3: Using EXPLAIN with count() function
The way of using EXPLAIN statement for the SELECT query with the count() function has been shown in this example. Here, the SELECT query will count the number of records that contain a credit value less than 2.
The following output will appear after executing the above query.
Example-4: Using EXPLAIN ANALYZE with count() function
The way of using EXPLAIN ANALYZE statement for the SELECT query with the count() function that has been used in the previous example has been shown in this example.
The following output will appear after executing the above query. The output shows detailed information about the query execution, such as cost value, actual time, loops, etc.
Conclusion:
Different uses of EXPLAIN and EXPLAIN ANALYZE statements in MySQL have been shown in this tutorial for the simple SELECT query and the SELECT query with WHERE clause to know the purpose of using these statements.
