Purging data when the table is big and has children tables

Let’s create the schema

PS8_Primary (none)> CREATE SCHEMA IF NOT EXISTS test;
Query OK, 1 row affected (0.06 sec)

PS8_Primary (none)> USE test;
Database changed

Then the tables

PS8_Primary test> CREATE TABLE orders (
OrderId int(11) NOT NULL AUTO_INCREMENT,
Job varchar(45) NOT NULL,
CustomerOrderNumber varchar(45) DEFAULT NULL,
OrderDate datetime DEFAULT NULL,
ProductCode varchar(45) NOT NULL,
CreatedDate datetime NOT NULL,
PRIMARY KEY (OrderId),
KEY IX_Orders_CreatedDate (CreatedDate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 2 warnings (0.52 sec)

PS8_Primary test> CREATE TABLE exceptions (
ExceptionId int(11) NOT NULL AUTO_INCREMENT,
CreatedDate datetime NOT NULL,
OrderId int(11) DEFAULT NULL,
Code int(11) NOT NULL,
Message varchar(255) NOT NULL,
PRIMARY KEY (ExceptionId),
CONSTRAINT FK_Exceptions_Orders_OrderId FOREIGN KEY (OrderId) REFERENCES orders (OrderId) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 4 warnings (0.12 sec)

PS8_Primary test> CREATE TABLE personalizations (
PersonalizationId int(11) NOT NULL AUTO_INCREMENT,
OrderId int(11) DEFAULT NULL,
SortOrder int(11) NOT NULL,
Text varchar(45) NOT NULL,
PRIMARY KEY (PersonalizationId),
CONSTRAINT OrderId FOREIGN KEY (OrderId) REFERENCES orders (OrderId) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 4 warnings (0.39 sec)


Let’s verify the child tables


PS8_Primary test> SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE REFERENCED_TABLE_NAME IN ('orders')\G
*************************** 1. row ***************************
       CONSTRAINT_CATALOG: def
        CONSTRAINT_SCHEMA: test
          CONSTRAINT_NAME: FK_Exceptions_Orders_OrderId
UNIQUE_CONSTRAINT_CATALOG: def
 UNIQUE_CONSTRAINT_SCHEMA: test
   UNIQUE_CONSTRAINT_NAME: PRIMARY
             MATCH_OPTION: NONE
              UPDATE_RULE: NO ACTION
              DELETE_RULE: NO ACTION
               TABLE_NAME: exceptions
    REFERENCED_TABLE_NAME: orders
*************************** 2. row ***************************
       CONSTRAINT_CATALOG: def
        CONSTRAINT_SCHEMA: test
          CONSTRAINT_NAME: OrderId
UNIQUE_CONSTRAINT_CATALOG: def
 UNIQUE_CONSTRAINT_SCHEMA: test
   UNIQUE_CONSTRAINT_NAME: PRIMARY
             MATCH_OPTION: NONE
              UPDATE_RULE: NO ACTION
              DELETE_RULE: NO ACTION
               TABLE_NAME: personalizations
    REFERENCED_TABLE_NAME: orders
2 rows in set (2.42 sec)


Have those child tables sub-child tables? NO (I checked in customer schema and they didn’t have - it’s just for information use)

PS8_Primary test> SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE REFERENCED_TABLE_NAME IN ('exceptions','personalizations');
Empty set (0.03 sec)

Let’s load the tables with some data (I won’t insert millions of rows due to my laptop limitation, but it works anyway)

[percona@PS8_Primary ~]$ for ROW in {1..10000}; do echo "INSERT INTO test.orders (OrderId,Job,CustomerOrderNumber,OrderDate,ProductCode,CreatedDate) VALUES ($ROW,'OrderNumber $ROW','Job $ROW',DATE(DATE_ADD(DATE_ADD(now(), INTERVAL -365 DAY),INTERVAL $ROW HOUR)),'Product Number $ROW',DATE(DATE_ADD(DATE_ADD(now(), INTERVAL -365 DAY),INTERVAL $ROW HOUR)));"; done | mysql test -A

[percona@PS8_Primary ~]$ for ROW in {1..10000}; do for NEST in {1..2}; do echo "INSERT INTO test.exceptions (CreatedDate,OrderId,Code,Message) VALUES (DATE(DATE_ADD(DATE_ADD(now(), INTERVAL -365 DAY),INTERVAL $ROW HOUR)),$ROW,222*$ROW,'Exception Detail for order # $ROW');"; done; done | mysql test -A

[percona@PS8_Primary ~]$ for ROW in {1..10000}; do for NEST in {1..2}; do echo "INSERT INTO test.personalizations (OrderId,SortOrder,Text) VALUES ($ROW,$NEST*$ROW,'Personalization Detail for OrderID # $ROW');"; done; done | mysql test -A