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

MySQL & MariaDB Percona Distribution for MySQL
1

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

 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

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

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:

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:

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.

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?

1 Like
2

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.

1 Like
3

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

EXPLAIN-s are:

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)

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)
1 Like
4

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

1 Like
5

@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.

2 Likes
6

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

1 Like