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)
