Skip to content

# Tip: faster than TRUNCATE

MySQL InnoDB SQL March 9, 2010

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.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
12
Leave a Reply

11 Comment threads
1 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
6 Comment authors
Recent comment authors

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

Subscribe
Notify of
Guest
Morgan Tocker

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

Try deleting a large file with ext3 versus renaming it. This will explain a lot of the problem 😉

Author
shlomi

Hi Morgan,

So simple… :D, thanks!

At least on two machines I have xfs for sure, though… So there’s still a small mystery…

Guest
Darius Jahandarie

Isn’t the table being dropped after renamed though? It should still have the same contention on the disk (if that was the original problem), as long as you are deleting the data.

Author
shlomi

@Darius,

Not a problem, since the dropped table has no contention: no one is writing to it.

Guest
Morgan Tocker

shlomi: The table renamed & dropped still has some contention issues (lock_open mutex will be held), but your trick is still useful for emptying a table and being able to use it again.

Guest
Vojtech Kurka

Morgan, large ibd files are not the only cause. Shlomi, It’s a bug, just look at: http://bugs.mysql.com/bug.php?id=35077 http://bugs.mysql.com/bug.php?id=51325 http://bugs.mysql.com/bug.php?id=51557

Author
shlomi

@Vojtech,
Thank you!

Guest
Hans-Henrik

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.

Author
shlomi

@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).

Guest
grace

hey shlomi,

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

Author
shlomi

@grace,
The RENAME itself should not be affected by number of rows, since it merely maps to a file rename. I say “should” though I’ve renamed very large tables with no overhead.

Log Buffer #182, A Carnival of the Vanities for DBAs

[…] Jamie Thomson asks why do you abbreviate schema names?. Shlomi Noach tries to solve the issue that “there is no consistent convention as for how to write [about table aliases in] an SQL query” in proper sql table alias use conventions. Noach also gives us a tip: faster than truncate. […]

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.