EXPLAIN EXTENDED Output Format
When EXPLAIN is used with the EXTENDED keyword, the output includes a filtered column not otherwise displayed.
This column indicates the estimated percentage of table rows that will be filtered by the table condition.

http://dev.mysql.com/doc/refman/5.6/en/explain-output.html#explain_filtered

mysql> explain select * from payment where payment_id >= 1 and payment_id <= 10;
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | payment | range | PRIMARY       | PRIMARY | 2       | NULL |   10 | Using where |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain extended select * from payment where payment_id >= 1 and payment_id <= 10;
+----+-------------+---------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | payment | range | PRIMARY       | PRIMARY | 2       | NULL |   10 |   100.00 | Using where |
+----+-------------+---------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `sakila`.`payment`.`payment_id` AS `payment_id`,`sakila`.`payment`.`customer_id` AS `customer_id`,`sakila`.`payment`.`staff_id` AS `staff_id`,`sakila`.`payment`.`rental_id` AS `rental_id`,`sakila`.`payment`.`amount` AS `amount`,`sakila`.`payment`.`payment_date` AS `payment_date`,`sakila`.`payment`.`last_update` AS `last_update` from `sakila`.`payment` where ((`sakila`.`payment`.`payment_id` >= 1) and (`sakila`.`payment`.`payment_id` <= 10)) |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

※ explain extendedでは、Warningが出るので内容確認する事で詳細がわかる。
※ filtered列にはデーブルに対して何%ぐらいがWHERE句などで絞り込まれるかの推測値が出力される。
The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition.
That is, rows shows the estimated number of rows examined and rows × filtered / 100 shows the number of rows
that will be joined with previous tables. This column is displayed if you use EXPLAIN EXTENDED.

Additional Note:

mysql> explain payment;
+--------------+----------------------+------+-----+-------------------+-----------------------------+
| Field        | Type                 | Null | Key | Default           | Extra                       |
+--------------+----------------------+------+-----+-------------------+-----------------------------+
| payment_id   | smallint(5) unsigned | NO   | PRI | NULL              | auto_increment              |
| customer_id  | smallint(5) unsigned | NO   | MUL | NULL              |                             |
| staff_id     | tinyint(3) unsigned  | NO   | MUL | NULL              |                             |
| rental_id    | int(11)              | YES  | MUL | NULL              |                             |
| amount       | decimal(5,2)         | NO   |     | NULL              |                             |
| payment_date | datetime             | NO   |     | NULL              |                             |
| last_update  | timestamp            | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+----------------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.01 sec)

mysql> desc payment;
+--------------+----------------------+------+-----+-------------------+-----------------------------+
| Field        | Type                 | Null | Key | Default           | Extra                       |
+--------------+----------------------+------+-----+-------------------+-----------------------------+
| payment_id   | smallint(5) unsigned | NO   | PRI | NULL              | auto_increment              |
| customer_id  | smallint(5) unsigned | NO   | MUL | NULL              |                             |
| staff_id     | tinyint(3) unsigned  | NO   | MUL | NULL              |                             |
| rental_id    | int(11)              | YES  | MUL | NULL              |                             |
| amount       | decimal(5,2)         | NO   |     | NULL              |                             |
| payment_date | datetime             | NO   |     | NULL              |                             |
| last_update  | timestamp            | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+----------------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.01 sec)

mysql>

Comments are closed.

Post Navigation