< WhatWebWhat Search >

Truncating big MySQL (InnoDB) tables

Truncating big tables, with millions of rows, in InnoDB can be really slow. Luckily there is a small trick to speed this up.

The trick is to first create a table that has the same structure as the table you are truncating:

CREATE TABLE `new_table` LIKE `bigtable`;

Then you can swap the to be truncated table for the empty table:

RENAME TABLE `bigtable` TO `old_table`, `new_table` TO `bigtable`;

After this your system will use the new (and empty) table, and you can do a quick drop of the old big table:

DROP TABLE `old_table`;

Done, you have succesfully truncated your huge table.

Articles Thursday, January 24, 2008