More about Removing Records (DELETE and TRUNCATE)

You already know how to remove a record with the DELETE statement. And you've probably taken note of the warning that if you don't use a WHERE clause, you'll remove all the records. A problem with removing all the records in that way is that it can be very slow in a large table! Luckily, there is a better way.

Let's first remove all the records in the customer_sales_value table with a DELETE statement:

mysql> DELETE FROM customer_sales_values; Query OK, 7 rows affected (0.00 sec)

The quicker way to remove this is by using TRUNCATE. Let's add the records back, and then use a TRUNCATE statement:

mysql> INSERT INTO customer_sales_values(first_name, surname, value,  value2) VALUES('Johnny', 'Chaka-Chaka', 500, NULL),('Patricia',  'Mankunku', 450, NULL), ('Winston', 'Powers', 750, NULL),('Yvonne',  'Clegg', 5800, NULL), ('Charles', 'Dube', 0, NULL), ('Charles',  'Dube', 0, NULL), ('Gladys', 'Malherbe', 5, 10); mysql> TRUNCATE customer_sales_values; Query OK, 0 rows affected (0.00 sec)

Notice the difference between the outputs of the two statements. DELETE informs you how many rows have been removed, but TRUNCATE doesn't; TRUNCATE just removes the lot without counting them. It actually does this by dropping and re-creating the table.