Tip: faster than TRUNCATE

MySQL

TRUNCATE is usually a fast operation (much faster than DELETE FROM). But sometimes it just hangs; I’ve has several such uncheerful events with InnoDB (Plugin) tables which were extensively written to. The TRUNCATE hanged; nothing else would work; minutes pass.

TRUNCATE on tables with no FOREIGN KEYs should act fast: it translate to dropping the table and creating a new one (and it all depends on the MySQL version, see the manual).

What’s faster than TRUNCATE, then? If you don’t have triggers nor FOREIGN KEYs, a RENAME TABLE can come to the rescue. Instead of:

TRUNCATE log_table

Do:

CREATE TABLE log_table_new LIKE log_table;
RENAME TABLE log_table TO log_table_old, log_table_new TO log_table;
DROP TABLE log_table_old;

I found this to work well for me. Do note that AUTO_INCREMENT values can be tricky here: the “new” table is created with an AUTO_INCREMENT value which is immediately taken in the “working” table. If you care about not using same AUTO_INCREMENT values, you can:

ALTER TABLE log_table_new AUTO_INCREMENT=some high enough value;

Just before renaming.

I do not have a good explanation as for why the RENAME TABLE succeeds to respond faster than TRUNCATE.

12 thoughts on “Tip: faster than TRUNCATE”

  3. The big difference, i think is the time the table ‘log_table’ is unavailable.

    If you truncate, the table will be locked until the space has been freed (8-10 seconds for _very_ large tables, if you have IO at the same time). If you do the renaming first, the empty table will appear _very_ fast, while the dropping of the old table does not involve locking of any table that is in use, and so – probably doesn’t matter much if it takes a few seconds.

  4. @Hans,
    Well, I would do well with 8-10 seconds. However, as I described, I waited for long minutes. Connections got into Locked state. Nothing would happen (on one occasion I waited for 15 minutes before calling it off).

  5. hey shlomi,

    just want to ask if number of records in the table affects renaming table performance?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.