Delete big data using MySQL Partitioning

What is MySQL Partitioning?

Partitioning is a way in which a database (MySQL in this case) splits its actual data down into separate tables, but still get treated as a single table by the SQL layer.

Partitions are also very useful in dealing with data rotation. If MySQL can easily identify rows to delete and map them to single partition, instead of running DELETE FROM table WHERE

There are 2 types of partitioning:

1. Vertical Partitioning
2. Horizontal Partitioning

Horizontal partitioning means that all rows matching the partitioning function will be assigned to different physical partitions. Vertical partitioning allows different table columns to be split into different physical partitions. Currently, MySQL supports horizontal partitioning, but not vertical.

Delete Millions of Records using MySql Table Partitioning Technique

Suppose we have table that have millions of records so we can ALTER the table and make table partitioning according to our needs.

Before Run any Action Query on Production Server:
1. Take backup of the table/database
2. Recommend to do this activity on low traffic (during night)
3. Perform this action on test server first before go to live server

ALTER TABLE `table_name` PARTITION BY RANGE (UNIX_TIMESTAMP(table_name_timestamp)) 
(PARTITION pYear2017 VALUES LESS THAN (UNIX_TIMESTAMP('2018-01-01 00:00:00')), 
PARTITION pYear2018 VALUES LESS THAN (UNIX_TIMESTAMP('2019-01-01 00:00:00')), 
PARTITION pYear2019 VALUES LESS THAN (MAXVALUE));

After running the above query we’ll have 3 partitions (pYear2017, pYear2018, pYear2019) of table as showing in below image.

As we can see there are many options for the Partitions. We can Analyze, Check, Optimize, Rebuild, Repair, Truncate and Drop the Partition.

We can see all the records of the selected partition using below query. I select pYear2018 partition.
This query will show all the records of year 2018

SELECT * FROM `table_name` PARTITION(pYear2018);

Now if we want to drop any partition This will also DELETE the data related to the selected partition(s). And if we truncate the partition records will delete but partition remain.

We can select any action (eg: Drop) and click on go to perform the action. Or we can run sql query by selection any partition eg: pYear2018

ALTER TABLE `table_name` DROP PARTITION pYear2018;

The above query will drop partition and delete all the records of year 2018 very fast.

We can use same technique to delete other records with other conditions as well.
This technique is very useful for the big data tables.