Why MySQL performs full table scan if subquery is used in update statement I have a test table:

create table t1 (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
    `description` varchar(120) CHARACTER SET utf8 DEFAULT NULL,
    PRIMARY KEY (`id`)) ENGINE=InnoDB;
insert into t1 (`description`)
         values ('d1'),('d2'),('d3'),('d4'),('d5'),
                ('d6'),('d7'),('d8'),('d9'),('d10');


Now let's look the following EXPLAINations of the query plans:

A:

mysql&gt; explain update t1 set description='new_description' where id in (5,6);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra                        |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
|  1 | UPDATE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | const |    2 |   100.00 | Using where; Using temporary |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
1 row in set (0.00 sec)


B:

mysql&gt; explain update t1 set description='new_description'
           where id in (select id
              from (select id from t1 where id in (5,6)) tmp);
+----+--------------------+------------+------------+----------------+---------------+-------------+---------+------+------+----------+------------------------------+
| id | select_type        | table      | partitions | type           | possible_keys | key         | key_len | ref  | rows | filtered | Extra                        |
+----+--------------------+------------+------------+----------------+---------------+-------------+---------+------+------+----------+------------------------------+
|  1 | UPDATE             | t1         | NULL       | index          | NULL          | PRIMARY     | 4       | NULL |   10 |   100.00 | Using where; Using temporary |
|  2 | DEPENDENT SUBQUERY | &lt;derived3&gt; | NULL       | index_subquery | &lt;auto_key0&gt;   | &lt;auto_key0&gt; | 4       | func |    2 |   100.00 | Using index                  |
|  3 | DERIVED            | t1         | NULL       | range          | PRIMARY       | PRIMARY     | 4       | NULL |    2 |   100.00 | Using where; Using index     |
+----+--------------------+------------+------------+----------------+---------------+-------------+---------+------+------+----------+------------------------------+
3 rows in set (0.01 sec)


These queries do the same thing, but query B uses subquery.

Could you please help me with understanding why in query "B" MySQL performs full table scan(10 rows)? As subquery returns only 2 rows.

UPDATE:
It seems that it is related not only to a subquery, but to "in" operator too:

mysql&gt; explain update t1 set description='new_description'
              where id = (select id from
                 (select id from t1 where id =5) tmp);
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | t1         | NULL       | range  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using where |
|  2 | SUBQUERY    | &lt;derived3&gt; | NULL       | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL        |
|  3 | DERIVED     | t1         | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
3 rows in set (0.00 sec)


AFAIS, with "=" operator MySQL process only 1 row.
IN ( SELECT ... ) has always been poorly optimized in MySQL.  There were improvements in 5.6, but it does not seem to cover your case.  File a bug with http://bugs.mysql.com if you like.

It is often better to change to a JOIN since it optimizes better.  (I assume your real query is more involved, and you simplified it for this Forum.)

You can verify whether it does a table scan:

FLUSH STATUS;
perform the operation
SHOW SESSION STATUS LIKE 'Handler%';


Meanwhile, the "rows" in EXPLAIN is pessimistic in some situations.  I would trust the Handler counts when in doubt.  With 15 rows instead of 10, I got (excluding 0 values):

| Handler_commit             | 1     |
| Handler_external_lock      | 4     |
| Handler_read_first         | 1     |
| Handler_read_key           | 18    |
| Handler_read_rnd_next      | 16    |
| Handler_update             | 2     |
| Handler_write              | 2     |


That smells like a tmp table with 2 rows, plus a full scan (maybe twice?) of 15 rows, plus some other miscellany.