The task of deleting the entire contents of a very large table or in a very old database is usually a bit cumbersome.
If it is a table with thousands or millions of records, the task becomes much more complex.
When you have to do something like this, aside from the fear of accidentally deleting something, the first thing you might think of is using an SQL DELETE statement.
DELETE statement
In other words, something as simple as writing this:
DELETE FROM MyBD.MyTable
The main drawback of using such an instruction is that it is very expensive to execute, from a computational point of view, since, clearly, what it does below is delete record by record, and generating a transaction for each of them.
And to top it off, it generates a lock for every delete statement (for every DELETE), which is more resource intensive.
With DELETE, if you have a few hundred or a few thousand rows, it will be very fast.
But if you have millions, the process can be very slow, affect the performance of the database server and will generate a huge transaction log.
However, there is an advantage to using DELETE, and if you don’t want to delete the entire contents of the table, it allows you to decide exactly what to delete, through the use of the WHERE statement.
But, as the title of this article says, what is sought is to eliminate ALL the content of a table, therefore, this solution is inadequate.
Another option, optimal for emptying a table of all its data is TRUNCATE the table.
TRUNCATE statement
Almost all data managers have a special instruction to do this:
TRUNCATE TABLE.
What this instruction does is exactly that: empty the table indicated of data. For example:
TRUNCATE TABLE MyBD.MyTable
Although it does the same thing as the DELETE statement, it has big differences with this one, which you will see below:
- Mode of operation: since it is not possible to restrict what is deleted (everything is always deleted), TRUNCATE does not scan the table record by record, nor does it block them or save each deletion in the transaction log.
- In fact, TRUNCATE deletes everything there is, locking the entire table and saving a single transaction to the log, thus reducing log usage to a bare minimum.
- It also locks the schema of the table, that is, the table structure cannot be modified while it is running, although, basically, the objective is to delete the records, and not to modify the table structure, both actions should not be planned at the same time, by way of advice.
- TRUNCATE is a DDL (Data Definition Language) statement and not a query.
- Therefore it does not return the number of records deleted, which DELETE does.
- Leave the autonumber fields with the initial value (normally 1), not being so in the case of DELETE.
- It does not execute triggers associated with data deletion, if any, DELETE does.
- It does not leave any pages of data hanging, which with DELETE can happen, leaving perhaps many blank pages taking up space.
- It doesn’t work with indexed views, while DELETE does.
- It cannot be used within a transaction.
- Regarding foreign keys, logically, you cannot delete the records of a table that have a foreign key with another and that have referential integrity. In that case, you would have to delete the dependent records first.
Example of using the TRUNCATE statement
Here you will see a simple example of the instruction, executed in MySQL Workbench, however, Workbench already has this instruction predefined.
The table to be used is ‘qualifications’ from the ‘alumni’ database, which has 10 thousand records.
The execution was immediate, according to the log records, which indicated 0.250 seconds.
It is recommended to make a backup, previously, before making modifications to the database, in case it is a production one.Here you will find the official information of TRUNCATE for MySQL