Faster method: TRUNCATE or DROP a table?

I am working a a project in the University and I need to re-create a table. For that task, I’m thinking in two ways: using TRUNCATE, or dropping the table and create it again.

TRUNCATE is a SQL sentence that empties a table, and for using it, you need to be granted as using DROP priviledge. autokey is reset to zero. The second option is using DROP a table, and create it again using CREATE TABLE.

Para valorar de las dos opciones es más rápida, vamos a utilizar PROFILE, para ver la velocidad a la que se ejecutan las consultas (esto ya lo vimos en Manejando Datos).

Table’s size

In the next image you can see the size of the table to be used in this experiment. Both tables are called datos.

Both has different number of rows: one has 35.416.094 rows and the other has 22.654.432, with a size of 5,7 Gb and 3.5 Gb:

Let0s start with TRUNCATE

Using TRUNCATE is very simple:

The time of the truncatation has been quite fast, dropping 23 millins records per minute!

Now, let’s use DROP TABLE

The second alternative is using DROP TABLE:

Tampoco está mal, pues 22 millones de registros han sido eliminados en menos de 1 segundo.

What if I use DELETE FROM?

Although I have worked with two tables, I hace a thrid one ready for using the sintax DELETE FROM. In this case, the table has 45.292.476 records:

Lets execute DELETE FROM datos and ….

The task turns to be very slow. When I did the capture it was 126 seconds, and still working!

So, the most effective is …TRUNCATE

That’s my opinion: the most effective way of removing all rows of a table is TRUNCATE because the table is regenerated, and you don’t need extra sentences (you need CREATE TABLE in case you use DROP).

I hope it can be useful for you! Happy coding!