# DELETE ... IN(SELECT..) freezes constantly

**URL:** <https://forums.percona.com/t/delete-in-select-freezes-constantly/10639>\
**Category:** Percona Distribution for MySQL\
**Created:** [May 31, 2021, 9:52am UTC](https://forums.percona.com/t/delete-in-select-freezes-constantly/10639 "2021-05-31T09:52:03Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Linuxtech](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/linuxtech/32/3630_2.png) [@Linuxtech](https://forums.percona.com/u/Linuxtech)\
**Post date:** [May 31, 2021, 9:52am UTC](https://forums.percona.com/t/delete-in-select-freezes-constantly/10639/1 "2021-05-31T09:52:03Z")

</div>

Hi, all. I’m experiencing strange issue in MySQL.  
I have 2 tables in database,

```auto
 oc_ocfilter_option_value_to_product | CREATE TABLE `oc_ocfilter_option_value_to_product` (
  `ocfilter_option_value_to_product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `option_id` int(11) NOT NULL,
  `value_id` bigint(20) NOT NULL,
  `slide_value_min` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `slide_value_max` decimal(15,4) NOT NULL DEFAULT '0.0000',
  PRIMARY KEY (`ocfilter_option_value_to_product_id`),
  UNIQUE KEY `option_id_value_id_product_id` (`option_id`,`value_id`,`product_id`),
  KEY `slide_value_min_slide_value_max` (`slide_value_min`,`slide_value_max`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=802118 DEFAULT CHARSET=utf8

```

```auto
oc_product_option_value | CREATE TABLE `oc_product_option_value` (
  `product_option_value_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_option_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `option_id` int(11) NOT NULL,
  `option_value_id` int(11) NOT NULL,
  `quantity` int(3) NOT NULL,
  `subtract` tinyint(1) NOT NULL,
  `price` decimal(15,4) NOT NULL,
  `price_prefix` varchar(1) NOT NULL,
  `points` int(8) NOT NULL,
  `points_prefix` varchar(1) NOT NULL,
  `weight` decimal(15,8) NOT NULL,
  `weight_prefix` varchar(1) NOT NULL,
  `optsku` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`product_option_value_id`),
  KEY `option_value_id` (`option_value_id`),
  KEY `product_id` (`product_id`),
  KEY `optsku` (`optsku`),
  KEY `optsku_2` (`optsku`),
  KEY `product_option_id` (`product_option_id`),
  KEY `optsku_3` (`optsku`)
) ENGINE=InnoDB AUTO_INCREMENT=222740 DEFAULT CHARSET=utf8

```

When I try to execute query

```auto
mysql> delete FROM oc_ocfilter_option_value_to_product WHERE option_id IN(SELECT option_id FROM oc_product_option_value WHERE quantity < '1');

```

it freezes. But the SELECT executes pretty fast:

```auto
mysql> select * FROM oc_ocfilter_option_value_to_product WHERE option_id IN(SELECT option_id FROM oc_product_option_value WHERE quantity < '1');
Empty set (0.02 sec)

```

SELECT subquesry returns approx 13000 rows with only two values:

```auto
mysql> SELECT option_id, count( option_id) FROM oc_product_option_value WHERE quantity < '1' group by option_id;
+-----------+-------------------+
| option_id | count( option_id) |
+-----------+-------------------+
| 15 | 6621 |
| 16 | 6395 |
+-----------+-------------------+
2 rows in set (0.02 sec)

```

I have already tried to use ‘DISTINCT’ in subquery, but no luck.

```auto
delete FROM oc_ocfilter_option_value_to_product WHERE option_id IN(SELECT distinct option_id FROM oc_product_option_value WHERE quantity < '1');

```

It reproduces on MySQL 5.6 and 5.7. Does someone know what the root of that issue?

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [May 31, 2021, 1:29pm UTC](https://forums.percona.com/t/delete-in-select-freezes-constantly/10639/2 "2021-05-31T13:29:38Z")

</div>

What does the EXPLAIN show?

I highly recommend you not use the WHERE IN (SELECT …) format. The official MySQL manual has an entire chapter on how to write sub-selects as JOINs for optimal performance.

---

<div class="post-metadata">

**Author:** ![Linuxtech](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/linuxtech/32/3630_2.png) [@Linuxtech](https://forums.percona.com/u/Linuxtech)\
**Post date:** [June 1, 2021, 11:43am UTC](https://forums.percona.com/t/delete-in-select-freezes-constantly/10639/3 "2021-06-01T11:43:46Z")

</div>

Hi, @matthewb .  
I have found this issue while debug OpenCart plugin.

EXPLAIN-s are:

```auto
mysql> explain DELETE FROM oc_ocfilter_option_value_to_product WHERE option_id IN(SELECT option_id FROM oc_product_option_value WHERE quantity < '1');
+----+--------------------+-------------------------------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------------------------------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | DELETE | oc_ocfilter_option_value_to_product | NULL | ALL | NULL | NULL | NULL | NULL | 122220 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | oc_product_option_value | NULL | ALL | NULL | NULL | NULL | NULL | 15637 | 3.33 | Using where |
+----+--------------------+-------------------------------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
2 rows in set (0.00 sec)

```

```auto
mysql> explain SELECT * FROM oc_ocfilter_option_value_to_product WHERE option_id IN(SELECT option_id FROM oc_product_option_value WHERE quantity < '1');
+----+--------------+-------------------------------------+------------+------+-------------------------------+-------------------------------+---------+-----------------------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------------------------------+------------+------+-------------------------------+-------------------------------+---------+-----------------------+-------+----------+-------------+
| 1 | SIMPLE | <subquery2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | NULL |
| 1 | SIMPLE | oc_ocfilter_option_value_to_product | NULL | ref | option_id_value_id_product_id | option_id_value_id_product_id | 4 | <subquery2>.option_id | 1771 | 100.00 | NULL |
| 2 | MATERIALIZED | oc_product_option_value | NULL | ALL | NULL | NULL | NULL | NULL | 15637 | 33.33 | Using where |
+----+--------------+-------------------------------------+------------+------+-------------------------------+-------------------------------+---------+-----------------------+-------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

```

---

<div class="post-metadata">

**Author:** ![Ivan\_Groenewold](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ivan_groenewold/32/6299_2.png) [@Ivan\_Groenewold](https://forums.percona.com/u/Ivan_Groenewold)\
**Post date:** [June 1, 2021, 12:17pm UTC](https://forums.percona.com/t/delete-in-select-freezes-constantly/10639/4 "2021-06-01T12:17:31Z")

</div>

As Matthew said, try writing this as a join rather than subselect.

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [June 1, 2021, 12:57pm UTC](https://forums.percona.com/t/delete-in-select-freezes-constantly/10639/5 "2021-06-01T12:57:14Z")

</div>

> [@Linuxtech](#):
>
> `DEPENDENT SUBQUERY`

@Linuxtech , the phrase ‘DEPENDENT SUBQUERY’ says that for each parent row, I need to execute this many times. You’ve got insane query volume in that 1 query. You’re looking at well over 1B query executions for that 1 DELETE. Optimize the SQL. That’s your problem.

---

<div class="post-metadata">

**Author:** ![Linuxtech](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/linuxtech/32/3630_2.png) [@Linuxtech](https://forums.percona.com/u/Linuxtech)\
**Post date:** [June 1, 2021, 2:11pm UTC](https://forums.percona.com/t/delete-in-select-freezes-constantly/10639/6 "2021-06-01T14:11:58Z")

</div>

@matthewb, @Ivan_Groenewold thanks for your explanations. Now it’s clear what to do.