This article demonstrates the usage of the EXPLAIN plan in SQL.
In this article, we’ll make a brief introduction to the EXPLAIN statement. The EXPLAIN statement is sort of a dry-run of your query or the blueprint/plan of how the server plans to execute your query. Generally, when complicated queries exhibit performance degradation, we can use the EXPLAIN statement to seek and identify bottlenecks. This lesson is not a comprehensive treatment of query optimization, rather just light and general introduction to a vast subject.
Note that EXPLAIN and DESCRIBE are synonyms, however, as a convention EXPLAIN is used with queries whereas DESCRIBE is with structures.
Syntax :
EXPLAIN <SQL Statement>;-- Query 1
EXPLAIN SELECT * FROM Actors;
-- Query 2
DESCRIBE SELECT Id FROM Actors;
-- Query 3
EXPLAIN SELECT * FROM Actors INNER JOIN DigitalAssets ON Id = ActorId;This Articles Contents
EXPLAIN SELECT * FROM table;
Let’s see what the server output does when EXPLAIN is used with a simple select-all statement as follows:
EXPLAIN SELECT * FROM Actors;
The output is a table with different columns. Let’s see what each column means:
| Column | Description |
| id | This is the sequential number of the SELECT within the query. |
| select_type | This is the type of select. For instance, it can be listed as SIMPLE if it doesn’t involve any queries. It can be PRIMARY if it’s the outermost select. There are also othervalues. For a complete list see this. |
| table | The name of the table, the rows come from. The value can also be derived if the table comes from a sub-query in the FROM clause. |
| partitions | The partitions from which records would be matched by the query. We don’t discuss partitioned tables in this course and this value will always be NULL for our queries. |
| type | The type of join. |
| possible_keys | This is one of the most useful pieces of information. It indicates the indexes from which MySQL can choose to find the rows in this table. If it possible_keys is NULL, it implies there are no relevanti ndexes. It also presents an opportunity to add an index based on the columns used in the WHERE clause on a slow-running query improve execution time. |
| key | The key column indicates the key (index) that MySQL actually decided to use. If MySQL decides to use one of the possible_keys indexes to lookup rows, that index is listed as the key-value. |
| key_len | The key_len column indicates the length of the key that MySQL decided to use. |
| ref | The ref column shows which columns or constants are compared to the index named in the key column to select rows from the table. |
| rows | Refers to the number of rows MySQL believes it must examine to execute the query. |
| filtered | The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. The maximum value is 100, which means no filtering of rows occurred. |
| Extra | This column contains additional information about how MySQL resolves the query. |
Now we can discuss the output of the EXPLAIN statement. The select_type is SIMPLE and no index is used in resolving the query. Furthermore, since MySQL estimates it will need to scan all eleven rows and none of the rows will be filtered.
DESCRIBE SELECT Id FROM table;
Now we’ll slightly tweak our query and select the ID column, which is also the primary key of the Actors table.
DESCRIBE SELECT Id FROM Actors;
Now MySQL says it will use the primary key to resolve the query. Also, note we have used DESCRIBE instead of EXPLAIN.
EXPLAIN SELECT * FROM table1 INNER JOIN table2 ON column1 = ActorId;
Here’s another example of an inner join between the DigitalAssets and Actors tables.
EXPLAIN SELECT * FROM Actors INNER JOIN DigitalAssets ON Id = ActorId;
Foreign Keys
In this lesson, we’ll discuss foreign keys. Not all storage engines support foreign keys. In MySQL, InnoDB supports foreign keys but MyISAM doesn’t. Let’s try to understand the concept through the example we have been working on in this course. The digital assets table consists of online accounts for actors in our Actors table. The two tables are related to each other by the columns ID and actor. Logically, it doesn’t make sense to have a row in the digital assets table for an actor who is not listed in our Actors table. Ideally, we would want that anytime we add a new row to the digital assets table a corresponding entry in the Actors table exists with the same ID as the actor of the new row being added to the digital assets table. Similarly, if we delete any actor from the Actors table we’d want all rows in the digital assets table associated with the actor to be deleted too. We can enforce these restrictions using the foreign key constraint.
The relation between the two tables is one too many. A foreign key can be a column or a group of columns in a table that link to a column or a group of columns in another table. In this case, the Actors table is the referenced table and called the parent table, whereas, the referencing table DigitalAssets is called the child table.
Example Syntax :
CREATE TABLE childTable (col1 <dataType>, col2 <dataType>, CONSTRAINT fkConstraint FOREIGN KEY (col2) REFERENCES parentTable(referencedCol);-- Query 1
ALTER TABLE DigitalAssets ADD FOREIGN KEY (ActorId) REFERENCES Actors(Id);
-- Query 2
INSERT INTO DigitalAssets VALUES ("www.dummy.url", "instagram", "2030-01-01 00:00:00", 100);ALTER TABLE table2 ADD FOREIGN KEY (ActorId) REFERENCES table1(Id);
We can declare a column as a foreign key in a child table only if the column has an index defined on it, i.e., the column is defined as a primary key, unique or key column in the parent table. If the column doesn’t have an index it can’t be used as a foreign key. For our example, we can alter our DigitalAssets and set the ActorID column to be the foreign key as follows:
ALTER TABLE DigitalAssets ADD FOREIGN KEY (ActorId) REFERENCES Actors(Id);
INSERT INTO table2 VALUES (“www.dummy.url”, “instagram”, “2030-01-01 00:00:00”, 100 );
Now if we add a row in the DigitalAssets table with an actor ID that doesn’t exist in the Actors table, an error is reported:
INSERT INTO DigitalAssets VALUES ("www.dummy.url", "instagram", "2030-01-01 00:00:00", 100 );
We can also create a foreign key constraint on a table itself. This is known as a recursive or self-referencing foreign key constraint. One example could be an employees table, which has a column to identify the manager of an employee. Since the manager is also an employee of the company, a row identifying him or her should also be present in the same table. The manager’s ID will reference the employee ID in the same column and the employee ID will act as a foreign key.