Advanced MySQL 8

Example of a mass DELETE

Let's try a mass delete on older data and see how much time it requires. But, first, we will add an index to the metric_timestamp column:

ALTER TABLE `test`.`metrics`ADD INDEX `index99` (`metric_timestamp`);ALTER TABLE `test`.`partitioned_metrics`ADD INDEX `index99` (`metric_timestamp`);

We like our tables with indexed data. Our next step is to perform a mass delete on the unpartitioned table:

DELETEFROM test.metricsWHERE metric_timestamp < '2017-01-01';

Roughly 0.8 seconds are required to delete 77,354 records. What happens if I execute a DROP partition command to get the same result in the partitioned table?

ALTER TABLE test.partitioned_metricsDROP PARTITION to_metric_logs

It takes less than 0.05 seconds. Let's ...