0

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?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
joeslice
  • 3,454
  • 1
  • 19
  • 24
  • What is the wierdness you find - any issues with truncate might well be overridden by this wierdness. – mmmmmm Sep 29 '09 at 10:01

1 Answers1

3

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

DVK
  • 126,886
  • 32
  • 213
  • 327