Slide 34
Slide 34 text
ちょっと別口の偏り(2)
WHERE .. IN .. の要素の数で実行計画は変わることがある
mysql> DELETE FROM post WHERE post_id IN (?, ?, ?, .., 15477);
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | post | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 15747 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.06 sec)
mysql> DELETE FROM post WHERE post_id IN (?, ?, ?, .., 15478);
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | DELETE | post | NULL | ALL | NULL | NULL | NULL | NULL | 99877 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 2 warnings (0.06 sec)
| Warning | 3170 | Memory capacity of 8388608 bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query. |
33/35