CODEWITHSUNDEEP
sqlmysqltruncate
joeslice
joeslice

Reputation: 3454

mysql Truncate table vs delete

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?

Upvotes: 0

Views: 3242

Answers (1)

DVK
DVK

Reputation: 129363

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

Upvotes: 3

Related Questions