UPDATE table1 SET col1 = X, col2 = Y WHERE id1 = 9 AND dt >= '2010-01-01';
SELECT col1, col2 FROM table1 WHERE id1 = 9 AND dt >= '2010-01-01';
mysql> EXPLAIN SELECT * FROM inventory WHERE item_id = 16102176\G; ********************* 1. row *********************** id: 1 select_type: SIMPLE table: inventory type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 787338 Extra: Using where
********************* 1. row *********************** id: 1 select_type: SIMPLE table: inventory type: ref possible_keys: item_id key: item_id key_len: 4 ref: const rows: 1 Extra:
********************* 1. row *********************** id: 1 select_type: SIMPLE table: p type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: ********************* 2. row *********************** id: 1 select_type: SIMPLE table: c type: ref possible_keys: parent_id key: parent_id key_len: 4 ref: const rows: 10 Extra:
mysql> SHOW SESSION STATUS LIKE 'Handler_read%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 0 | | Handler_read_key | 0 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 11 | +-----------------------+-------+ 7 rows in set (0.00 sec)
********************* 1. row *********************** id: 1 select_type: SIMPLE table: p type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 160 Extra: ********************* 2. row *********************** id: 1 select type: SIMPLE table: c type: ref possible_keys: PRIMARY,parent_id key: parent_id key_len: 4 ref: test.p.parent_id rows: 1 Extra: Using where
mysql> SHOW SESSION STATUS LIKE 'Handler_read%'; +--------------------------------------+---------+ | Variable_name | Value | +--------------------------------------+---------+ | Handler_read_first | 1 | | Handler_read_key | 164 | | Handler_read_last | 0 | | Handler_read_next | 107 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 161 | +--------------------------------------+---------+ 相关的QEP 列还包括key列。
CREATE TABLE `wp_posts` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `post_status` varchar(20) NOT NULL DEFAULT 'publish' , `post_type` varchar(20) NOT NULL DEFAULT 'post', PRIMARY KEY (`ID`), KEY `type_status_date`(`post_type`,`post_status`,`post_date`,`ID`) ) DEFAULT CHARSET=utf8 CREATE TABLE `wp_posts` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `post_status` varchar(20) NOT NULL DEFAULT 'publish' , `post_type` varchar(20) NOT NULL DEFAULT 'post', PRIMARY KEY (`ID`), KEY `type_status_date`(`post_type`,`post_status`,`post_date`,`ID`) ) DEFAULT CHARSET=utf8
mysql> EXPLAIN SELECT ID, post_title -> FROM wp_posts -> WHERE post_type='post' -> AND post_status='publish' -> AND post_date > '2010-06-01';
mysql> EXPLAIN SELECT p.* -> FROM parent p -> WHERE p.id NOT IN (SELECT c.parent_id FROM child c)\G ********************* 1. row *********************** id: 1 select type: PRIMARY table: p type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 160 Extra: Using where ********************* 2. row *********************** id: 2 select_type: DEPENDENT SUBQUERY table: c type: index_subquery possible_keys: parent_id key: parent_id key_len: 4 ref: func rows: 1 Extra: Using index 2 rows in set (0.00 sec) EXPLAIN SELECT p.* FROM parent p LEFT JOIN child c ON p.id = c.parent_id WHERE c.child_id IS NULL\G ********************* 1. row *********************** id: 1 select_type: SIMPLE table: p type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 160 Extra: ********************* 2. row *********************** id: 1 select_type: SIMPLE table: c type: ref possible_keys: parent_id key: parent_id key_len: 4 ref: test.p.id rows: 1 Extra: Using where; Using index; Not exists 2 rows in set (0.00 sec)
