Why delete with "where in" condition is slow, while looping & using single where is fast? I have a table with ~5 million rows. Here is create table statement:
CREATE TABLE `monitor_checks` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `monitor_id` int unsigned NOT NULL,
  `node_id` int unsigned NOT NULL,
  `url` varchar(2048) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `ip` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `request_method` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `request_body_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `request_body_raw` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `request_body_json` json DEFAULT NULL,
  `request_body_form_params` json DEFAULT NULL,
  `request_headers` json DEFAULT NULL,
  `response_protocol` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `response_code` int DEFAULT NULL,
  `response_reason_phrase` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `response_size` int DEFAULT NULL,
  `response_headers` json DEFAULT NULL,
  `is_successful` tinyint(1) NOT NULL,
  `is_failed` tinyint(1) NOT NULL,
  `fail_reason` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `response_time` double(8,6) DEFAULT NULL,
  `time_namelookup` decimal(8,6) DEFAULT NULL,
  `time_connect` decimal(8,6) DEFAULT NULL,
  `time_appconnect` decimal(8,6) DEFAULT NULL,
  `time_pretransfer` decimal(8,6) DEFAULT NULL,
  `time_starttransfer` decimal(8,6) DEFAULT NULL,
  `time_total` decimal(8,6) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `monitor_checks_monitor_id_created_at_index` (`monitor_id`,`created_at`),
  KEY `monitor_checks_node_id_foreign` (`node_id`),
  KEY `monitor_checks_monitor_id_is_failed` (`monitor_id`,`is_failed`),
  KEY `monitor_id_node_id_is_successful` (`monitor_id`,`node_id`,`is_successful`),
  KEY `monitor_checks_monitor_id_index` (`monitor_id`),
  CONSTRAINT `monitor_checks_monitor_id_foreign` FOREIGN KEY (`monitor_id`) REFERENCES `monitors` (`id`) ON DELETE CASCADE,
  CONSTRAINT `monitor_checks_node_id_foreign` FOREIGN KEY (`node_id`) REFERENCES `nodes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=224279831 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

I won't pretend that I know much about indexes, so maybe it's a complete mess. But still, I have the index: KEY monitor_id_node_id_is_successful (monitor_id,node_id,is_successful)
Now, when I execute this statement:
DELETE from monitor_checks where monitor_id in (~200 ids) and node_id = 1 and is_successful = 1
It takes a lot of time, a few minutes.
Now, if instead, I do 200 delete queries, looks like it takes less than a second.
I'm not even sure, I use Laravel framework and all I did is changed my code so that it loops through all values and deletes one-by-one.
Now if the show processlist I barely can see that the statement takes more than a second, while logs say that the whole loop for ~200 ids took a second.
Why so? I thought that if I use batch delete it should be faster?
UPD:
explain statement for where in returns:

Type: range

Possible indexes:


monitor_checks_monitor_id_created_at_index,monitor_checks_node_id_foreign,monitor_checks_monitor_id_is_failed,monitor_id_node_id_is_successful,monitor_checks_monitor_id_index

key:

monitor_checks_monitor_id_created_at_index

key len: 4

Rows: 73485
When building an index, start with the things that are tested with =.  That is, the index should be
 INDEX(node_id, is_successful,   -- in either order
       monitor_id)               -- last

Another tip:  When you have both INDEX(a) and INDEX(a,b), the former is unnecessary since the latter can handle searching just by a.  (But not just b.)  That is, currently your single-column index (monitor_id) can be DROPped.
More on building indexes:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql