A trick to delete a large MySQL table in InnoDB

Truncating large tables, with millions of rows, in InnoDB can be very tedious. Fortunately, there is a little trick to speed up the process. This trick only makes sense with databases over 1GB. The trick is to first create a table with the same structure as the table we are shrinking.

Note: Watch out for foreign keys here.

CREATE TABLE "new_table" LIKE "big_table";

Then we replace the old table with the new one.

RENAME TABLE "big_table" TO "old_table", "new_table" TO "big_table";

After this step, the system will use the new (empty) table, and now we can quickly delete the old table.

DROP TABLE "old_table";

Done.