Mega Code Archive
Understand the EXPLAIN statement
<![CDATA[
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>
]]>