Space constraint has been an endless and painstaking process for many of us, especially in systems that have a high number of transactions and data growth exceeding hundreds of GBs in a matter of days. In this blog, I will share a solution to remove this space and remove rows from tables in a few seconds regardless of the size of a table without causing any additional load on the database using table partitions.
The first approach that comes to anyone’s mind for deleting the row is using a DELETE query in SQL. Suppose, one wants to delete rows from a table that are older than one year—the query for such operations would be like this:
DELETE FROM salaries WHERE from_date <DATE_SUB(NOW(),INTERVAL 1 YEAR);
The above query is pretty straightforward but there are a few caveats:
- Server business will grow exponentially and could impact the usual traffic on the server.
- To speed up the above query …