Description:
CREATE TABLE `t2` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` char(10) DEFAULT NULL,
`d` varchar(256) DEFAULT NULL,
`e` text,
KEY `ii1` (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
sql1: update t2 set b = 3 where a = 1;
sql2: select b from t2 where a = 1;
sql2 will use index condition pushdown, but sql1 doesn't, but why, is that data format between engine and server transformation?
How to repeat:
mysql> explain select b from t2 where a = 1;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | t2 | NULL | ref | ii1 | ii1 | 5 | const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain update t2 set b = 3 where a = 1;
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+
| 1 | UPDATE | t2 | NULL | range | ii1 | ii1 | 5 | const | 1 | 100.00 | Using where; Using temporary |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+------------------------------+
1 row in set (0.00 sec)
