Mysql: Which to Use When: Drop Table, Truncate Table, Delete from Table

Mysql: Which to use when: drop table, truncate table, delete from table

• drop table tablename;
  
  • After this, it's gone. No more table. No more data.
  • Use this when you don't need that table any more.
• truncate table tablename;
  
  • After this, the table is empty, and (importantly) auto-incrementing keys are reset to 1. It's quite literally like having a brand new table.
  • Use this when you just want an empty table. It's faster than DELETE because it simply deletes all data. DELETE will scan the table to generate a count of rows that were affected.
• delete from tablename;
  
  • This lets you filter which rows to delete based on an optional WHERE clause.
  • Use this when you want to delete specific records, eg: DELETE FROM tablename WHERE username = 'joe'

TRUNCATE v/s DROP

There is lots of information available on the difference of both ( see here). However for the second part of your question regarding the performance - you should not worry that much for truncate vs drop. The reason being is it's more about the functionality you are expecting from truncate vs drop. You 'drop' when you don't intend to use the table anymore. You 'truncate' when you just want the table definition intact , you just want to delete the table data fast. The perceived difference in performance may come from the implementation details ( which can sometime be a bug like this) in the database.

MySQL: Does `DROP TABLE` completely remove the table or just the structure?

Using DROP TABLE will remove the entire table and all records contained inside it. If you want to retain the table structure but remove all data, then consider using TRUNCATE TABLE. Truncating a table is implemented by dropping the entire table and then recreating it. This is faster than doing DELETE FROM yourTable, which removes records one-by-one.

Delete, Truncate or Drop to clean out a table in MySQL

TRUNCATE will reset your auto-increment seed (on InnoDB tables, at least), although you could note its value before truncating and re-set accordingly afterwards using alter table:

ALTER TABLE t2 AUTO_INCREMENT = value

mysql large table delete

Since you want to delete most of the rows in the table, I would recommend moving the rows you want to keep to a temporary table, truncating the table, then re-importing. This still requires downtime on the table (that is, there should be no reads or writes to the table while the operation is in progress), but should be an order of magnitude faster than deleting row by row.

create table tmptable as select * from mytable where ...;
truncate table mytable;
insert into mytable select * from tmptable;
drop table tmptable;

Implictely, truncate table drops and recreates the table, which will reclaim the space. The documentation has a few interesting bullet point as regard to your question, such as:

Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.

Difference between drop table and truncate table?

Deleting records from a table logs every deletion and executes delete triggers for the records deleted. Truncate is a more powerful command that empties a table without logging each row. SQL Server prevents you from truncating a table with foreign keys referencing it, because of the need to check the foreign keys on each row.

Truncate is normally ultra-fast, ideal for cleaning out data from a temporary table. It does preserve the structure of the table for future use.

If you actually want to remove the table definitions as well as the data, simply drop the tables.

See this MSDN article for more info

---

Related Topics