![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
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>