Quickest way to delete enormous MySQL table

(As this turned up high in Google's results, I thought a little more instruction might be handy.)

MySQL has a convenient way to create empty tables like existing tables, and an atomic table rename command. Together, this is a fast way to clear out data:

CREATE TABLE new_foo LIKE foo;

RENAME TABLE foo TO old_foo, new_foo TO foo;

DROP TABLE old_foo;

Done

The quickest way is to use DROP TABLE to drop the table completely and recreate it using the same definition. If you have no foreign key constraints on the table then you should do that.

If you're using MySQL version greater than 5.0.3, this will happen automatically with a TRUNCATE. You might get some useful information out of the manual as well, it describes how a TRUNCATE works with FK constraints. http://dev.mysql.com/doc/refman/5.0/en/truncate-table.html

EDIT: TRUNCATE is not the same as a drop or a DELETE FROM. For those that are confused about the differences, please check the manual link above. TRUNCATE will act the same as a drop if it can (if there are no FK's), otherwise it acts like a DELETE FROM with no where clause.

EDIT: If you have a large table, your MariaDB/MySQL is running with a binlog_format as ROW and you execute a DELETE without a predicate/WHERE clause, you are going to have issues to keep up the replication or even, to keep your Galera nodes running without hitting a flow control state. Also, binary logs can get your disk full. Be careful.

The best way I have found of doing this with MySQL is:

DELETE from table_name LIMIT 1000;

Or 10,000 (depending on how fast it happens).

Put that in a loop until all the rows are deleted.

Please do try this as it will actually work. It will take some time, but it will work.

Couldn't you grab the schema drop the table and recreate it?