MySQL delete speed experiments.sql

-- Ran on n1-standard-8 with 500GB SSD on GCP
-- https://console.cloud.google.com/marketplace/details/click-to-deploy-images/mysql?q=mysql&id=59e776b5-96fb-4644-8a6e-92c2756ebef5

-- Create a database `test` and switch to it
create database test;
use test;

-- Create a table `target` and fill it with 100k rows
create table target (id varchar(20) primary key, name varchar(2));

delimiter $$
create procedure fill_target()
begin
  declare i int default 0;

  while i < 100000 do -- 100k rows
    insert ignore into target (id, name) values (floor(rand()*1000*1000*1000*1000), floor(rand()*1000*1000*1000*1000));
    set i = i + 1;
  end while;
end$$
delimiter ;

call fill_target();

-- Create a table `staging` and copy 10k rows from target
create table staging (id varchar(20) primary key, name varchar(20));
insert into staging (id, name) select id, name from target order by rand() limit 10000;

-- Create copies of target and staging without a primary key
create table target_no_key as select * from target;
create table staging_no_key as select * from staging;

-- Target has index, staging has index
explain extended delete target
from target
inner join staging on target.id = staging.id;
-- +----+-------------+---------+------------+--------+---------------+---------+---------+-----------------+-------+----------+-------------+
-- | id | select_type | table   | partitions | type   | possible_keys | key     | key_len | ref             | rows  | filtered | Extra       |
-- +----+-------------+---------+------------+--------+---------------+---------+---------+-----------------+-------+----------+-------------+
-- |  1 | SIMPLE      | staging | NULL       | index  | PRIMARY       | PRIMARY | 22      | NULL            | 10000 |   100.00 | Using index |
-- |  1 | DELETE      | target  | NULL       | eq_ref | PRIMARY       | PRIMARY | 22      | test.staging.id |     1 |   100.00 | NULL        |
-- +----+-------------+---------+------------+--------+---------------+---------+---------+-----------------+-------+----------+-------------+
delete target
from target
inner join staging on target.id = staging.id;
-- Query OK, 10000 rows affected (0.08 sec)

-- Target has index, staging does not
explain extended delete target
from target
inner join staging_no_key on target.id = staging_no_key.id;
-- +----+-------------+----------------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------+
-- | id | select_type | table          | partitions | type   | possible_keys | key     | key_len | ref                    | rows | filtered | Extra |
-- +----+-------------+----------------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------+
-- |  1 | SIMPLE      | staging_no_key | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                   | 9998 |   100.00 | NULL  |
-- |  1 | DELETE      | target         | NULL       | eq_ref | PRIMARY       | PRIMARY | 22      | test.staging_no_key.id |    1 |   100.00 | NULL  |
-- +----+-------------+----------------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------+
insert into target (id, name)
select id, name from staging;
-- Query OK, 10000 rows affected (0.06 sec)
-- Records: 10000  Duplicates: 0  Warnings: 0
delete target
from target
inner join staging_no_key on target.id = staging_no_key.id;
-- Query OK, 10000 rows affected (0.06 sec)

-- Neither has index
explain extended delete target_no_key
from target_no_key
inner join staging_no_key on target_no_key.id = staging_no_key.id;
-- +----+-------------+----------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
-- | id | select_type | table          | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
-- +----+-------------+----------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
-- |  1 | SIMPLE      | staging_no_key | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  9998 |   100.00 | NULL        |
-- |  1 | DELETE      | target_no_key  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99267 |    10.00 | Using where |
-- +----+-------------+----------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
delete target_no_key
from target_no_key
inner join staging_no_key on target_no_key.id = staging_no_key.id;
-- Gave up after a few minutes
create index target_id on target_no_key (id);
explain extended delete target_no_key
from target_no_key
inner join staging_no_key on target_no_key.id = staging_no_key.id;
-- +----+-------------+----------------+------------+------+---------------+-----------+---------+------------------------+------+----------+-------+
-- | id | select_type | table          | partitions | type | possible_keys | key       | key_len | ref                    | rows | filtered | Extra |
-- +----+-------------+----------------+------------+------+---------------+-----------+---------+------------------------+------+----------+-------+
-- |  1 | SIMPLE      | staging_no_key | NULL       | ALL  | NULL          | NULL      | NULL    | NULL                   | 9998 |   100.00 | NULL  |
-- |  1 | DELETE      | target_no_key  | NULL       | ref  | target_id     | target_id | 22      | test.staging_no_key.id |    1 |   100.00 | NULL  |
-- +----+-------------+----------------+------------+------+---------------+-----------+---------+------------------------+------+----------+-------+
delete target_no_key
from target_no_key
inner join staging_no_key on target_no_key.id = staging_no_key.id;
-- Query OK, 10000 rows affected (0.21 sec)