Why DELETE IN Subqueries Skip Indexes in MySQL and How to Optimize Them

The author introduces a production issue where a DELETE FROM account WHERE name IN (SELECT name FROM old_account) statement on MySQL 5.7 scans the entire account table instead of using the idx_name index, contrary to the expectation that the subquery would be indexed.

Two tables old_account and account are defined with identical structures, each having a primary key on id and an index on name . The problematic DELETE statement and its EXPLAIN output show a full‑table scan on account followed by row‑by‑row subquery evaluation.

CREATE TABLE `old_account` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `name` varchar(255) DEFAULT NULL COMMENT '账户名',
  `balance` int(11) DEFAULT NULL COMMENT '余额',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='老的账户表';

CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `name` varchar(255) DEFAULT NULL COMMENT '账户名',
  `balance` int(11) DEFAULT NULL COMMENT '余额',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';

When the same condition is used in a SELECT statement, MySQL rewrites the subquery as a semi‑join, allowing the index to be used. The EXPLAIN of the SELECT shows a semi join with the index on name .

explain select * from account where name in (select name from old_account);
show WARNINGS;

The optimizer output reveals the transformation: select `test2`.`account`.`id` AS `id`, `test2`.`account`.`name` AS `name`, `test2`.`account`.`balance` AS `balance`, `test2`.`account`.`create_time` AS `create_time`, `test2`.`account`.`update_time` AS `update_time` from `test2`.`account` semi join (`test2`.`old_account`) where (`test2`.`account`.`name` = `test2`.`old_account`.`name`) Thus, SELECT IN benefits from the semi‑join optimization, while DELETE IN does not.

To make the DELETE use the index, the author recommends rewriting it as a JOIN or adding an alias to the target table, which triggers the LooseScan execution strategy—a form of semi‑join that can use the index.

explain delete a from account as a where a.name in (select name from old_account);

The corresponding EXPLAIN shows the LooseScan strategy, confirming that the indexed access path is now taken.

In summary, DELETE IN subqueries in MySQL 5.7 do not get the semi‑join rewrite, leading to full scans; converting the statement to a JOIN or using a table alias forces the optimizer to apply an indexed plan, solving the performance problem.