Mysql – Delete millions of rows based on ids in another table

deleteMySQL

I have two tables on MySQL:

HISTORY_TABLE (4 million rows) (column1 indexed)
EXTRACT_TABLE (600K rows) (column1 indexed)

For every EXTRACT_TABLE.column1 there are multiple HISTORY_TABLE.column1 rows.

Objective: I want to delete all records that MATCH criteria:

HISTORY_TABLE.column1 = EXTRACT_TABLE.column1

I tried two different possibilities:

Simple query that matches criteria (10 hours)
Copying the NOT EXISTS rows into a new table (2days+)

I found via searching that deletion can be done in chunks with a stored procedure, but I don't know how.

Is there an example on how to do a cursor based on EXTRACT_TABLE to read in chunks and delete the other table? Or is there other way to accomplish my objective?

Best Answer

Since you are deleting a large percentage of the table, it would be better to build a new table with the rows to keep:

CREATE TABLE new LIKE real;
INSERT INTO new
    SELECT * FROM real
        LEFT JOIN extract  ON ...
        WHERE ... IS NULL;
RENAME TABLE real TO old,
             new TO real;    -- swap tables (sort of)
DROP TABLE old;              -- clean up

Even that may be too invasive. See the following for techniques for deleting "in chunks". They won't finish "fast", but they won't have nearly as much impact on other activity. http://mysql.rjweb.org/doc.php/deletebig

(FOREIGN KEYs and TRIGGERs are likely to cause trouble.)

Related Solutions

Oracle 11g – Best Way to Delete Very Large Recordsets

The logic with 'A' and 'B' might be "hidden" behind a virtual column on which you could do the partitioning:

alter session set nls_date_format = 'yyyy-mm-dd';
drop   table tq84_partitioned_table;

create table tq84_partitioned_table (
  status varchar2(1)          not null check (status in ('A', 'B')),
  date_a          date        not null,
  date_b          date        not null,
  date_too_old    date as
                       (  case status
                                 when 'A' then add_months(date_a, -7*12)
                                 when 'B' then            date_b
                                 end
                        ) virtual,
  data            varchar2(100) 
)
partition   by range  (date_too_old) 
( 
  partition p_before_2000_10 values less than (date '2000-10-01'),
  partition p_before_2000_11 values less than (date '2000-11-01'),
  partition p_before_2000_12 values less than (date '2000-12-01'),
  --
  partition p_before_2001_01 values less than (date '2001-01-01'),
  partition p_before_2001_02 values less than (date '2001-02-01'),
  partition p_before_2001_03 values less than (date '2001-03-01'),
  partition p_before_2001_04 values less than (date '2001-04-01'),
  partition p_before_2001_05 values less than (date '2001-05-01'),
  partition p_before_2001_06 values less than (date '2001-06-01'),
  -- and so on and so forth..
  partition p_ values less than (maxvalue)
);

insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('B', date '2008-04-14', date '2000-05-17', 
 'B and 2000-05-17 is older than 10 yrs, must be deleted');


insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('B', date '1999-09-19', date '2004-02-12', 
 'B and 2004-02-12 is younger than 10 yrs, must be kept');


insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('A', date '2000-06-16', date '2010-01-01', 
 'A and 2000-06-16 is older than 3 yrs, must be deleted');


insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('A', date '2009-06-09', date '1999-08-28', 
 'A and 2009-06-09 is younger than 3 yrs, must be kept');

select * from tq84_partitioned_table order by date_too_old;

-- drop partitions older than 10 or 3 years, respectively:

alter table tq84_partitioned_table drop partition p_before_2000_10;
alter table tq84_partitioned_table drop partition p_before_2000_11;
alter table tq84_partitioned_table drop partition p2000_12;

select * from tq84_partitioned_table order by date_too_old;

Postgresql – Disk file effects of delete and vacuum

You can save on the delete and vacuum if you will use smart partitioning! Make sure your design is compatible with dropping partitions instead of actual DELETE.

Best Answer

Related Solutions

Oracle 11g – Best Way to Delete Very Large Recordsets

Postgresql – Disk file effects of delete and vacuum

Related Question