mysql Truncate table vs delete

Question

We are about to deploy some code that truncates tables in our mysql 4 database. We are doing this to get around some replication 'weirdness' that we are seeing (and is widely known) with temp tables. My knee-jerk reaction when I saw this in a code review was "no", but I'm having trouble backing it up.

So, the question is: am I just overreacting? Have you run into any gotchas using truncate table in mysql that we should try to avoid? How about truncate in a replicated environment?

Answer 1

Truncation (at least in Sybase, not 100% sure about mySql)

• Does NOT write to the log. Meaning, no recovery and no rollbacks.

• Does not automatically rebuild the index statistics, meaning that the optimizer may not work correctly when looking at a given table.

Please see this article for mySQL specific details/comparison

Here's the relevant mySQL TFM