---
# System prepended metadata

title: Migration of large tables in MySQL

---

# Migration of large tables in MySQL

## problem
- Tables with hundreds of millions of rows are very slow to migrate even the smallest change. 

    Table: contacts_tags
    total rows:  675_783_453
    total non deleted rows: 447_172_940
    total deleted rows: 228_610_513

- Testing on a test-clone:

```sql
 ALTER TABLE `ap_prod`.`contacts_tags` 
 ADD source_created_at DATETIME(6) DEFAULT NULL;
```


## Property Inspections table

```sql
-- roughly 20m
ALTER TABLE property_inspections ADD integration_id INT DEFAULT NULL;

-- took 2.5 hours
ALTER TABLE property_inspections ADD FOREIGN KEY (integration_id) REFERENCES integrations(id);

-- took maybe 30m
CREATE INDEX index_property_inspections_on_deleted_integration_and_start ON property_inspections (deleted_at, integration_id, start_at);

-- took a while but this can be batched
UPDATE
	property_inspections
	JOIN property_listings ON property_listings.id = property_inspections.property_listing_id
	JOIN integrations ON integrations.id = property_listings.integration_id
SET
	property_inspections.integration_id = integrations.id
WHERE
	property_inspections.deleted_at IS NULL;
```

Given that the longest running command there is the one for adding the foreign key constraint, it's worth thinking about how we could make that faster. First, the obvious question is: why is it so slow in the first place? The answer is that it needs to verify that all the current values actually refer to a record in the integrations table. But each value is going to be null because we only just added the column and set it to null! In this case, we can use `SET FOREIGN_KEY_CHECKS=0;` to skip that check and hopefully it won't take as long. Then we can run `SET FOREIGN_KEY_CHECKS=1;` after (both these commands operate at the level of the session, not the instance itself).

Doing this our foreign key statements takes 36 seconds! What an improvement!