Understand the EXPLAIN statement : EXPLAIN « Command MySQL « SQL / MySQL

1. SQL / MySQL
2. Command MySQL
3. EXPLAIN

Understand the EXPLAIN statement

      
mysql>
mysql>
mysql> CREATE TABLE Manufacturers
    -> (
    ->     ManfID CHAR(8)NOT NULL PRIMARY KEY,
    ->     ManfName VARCHAR(30) NOT NULL
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>
mysql> INSERT INTO Manufacturers VALUES
    -> ('abc123', 'ABC Manufacturing'),
    -> ('def456', 'DEF Inc.'),
    -> ('ghi789', 'GHI Corporation'),
    -> ('jkl123', 'JKL Limited'),
    -> ('mno456', 'MNO Company');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE TABLE Parts
    -> (
    ->     PartID SMALLINT NOT NULL PRIMARY KEY,
    ->     PartName VARCHAR(30) NOT NULL,
    ->     ManfID CHAR(8) NOT NULL
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql> INSERT INTO Parts VALUES
    -> (101, 'DVD burner', 'abc123'),
    -> (102, 'CD drive', 'jkl123'),
    -> (103, '80-GB hard disk', 'mno456'),
    -> (104, 'Mini-tower', 'ghi789'),
    -> (105, 'Power supply', 'def456'),
    -> (106, 'LCD monitor', 'mno456'),
    -> (107, 'Zip drive', 'ghi789'),
    -> (108, 'Floppy drive', 'jkl123'),
    -> (109, 'Network adapter', 'def456'),
    -> (110, 'Network hub', 'jkl123'),
    -> (111, 'Router', 'mno456'),
    -> (112, 'Sound card', 'ghi789'),
    -> (113, 'Standard keyboard', 'mno456'),
    -> (114, 'PS/2 mouse', 'jkl123'),
    -> (115, '56-K modem', 'ghi789'),
    -> (116, 'Display adapter', 'mno456'),
    -> (117, 'IDE controller', 'def456');
Query OK, 17 rows affected (0.00 sec)
Records: 17  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> SELECT PartName, ManfName
    -> FROM Parts AS p, Manufacturers as m
    -> WHERE p.ManfID = m.ManfID
    -> ORDER BY PartName;
+-------------------+-------------------+
| PartName          | ManfName          |
+-------------------+-------------------+
| 56-K modem        | GHI Corporation   |
| 80-GB hard disk   | MNO Company       |
| CD drive          | JKL Limited       |
| Display adapter   | MNO Company       |
| DVD burner        | ABC Manufacturing |
| Floppy drive      | JKL Limited       |
| IDE controller    | DEF Inc.          |
| LCD monitor       | MNO Company       |
| Mini-tower        | GHI Corporation   |
| Network adapter   | DEF Inc.          |
| Network hub       | JKL Limited       |
| Power supply      | DEF Inc.          |
| PS/2 mouse        | JKL Limited       |
| Router            | MNO Company       |
| Sound card        | GHI Corporation   |
| Standard keyboard | MNO Company       |
| Zip drive         | GHI Corporation   |
+-------------------+-------------------+
17 rows in set (0.00 sec)

mysql>
mysql>
mysql> EXPLAIN SELECT PartName, ManfName
    -> FROM Parts AS p, Manufacturers as m
    -> WHERE p.ManfID = m.ManfID
    -> ORDER BY PartName;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | p     | ALL  | NULL          | NULL | NULL    | NULL |   17 | Using temporary; Using filesort |
|  1 | SIMPLE      | m     | ALL  | PRIMARY       | NULL | NULL    | NULL |    5 | Using where; Using join buffer  |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
2 rows in set (0.00 sec)

mysql>
mysql> drop table Parts;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table Manufacturers;
Query OK, 0 rows affected (0.00 sec)

mysql>

   
    
    
    
    
    
  

Related examples in the same category