List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:August 10 2001 7:34pm
Subject:TRUNCATE TABLE table_name
View as plain text  
Hi!

>>>>> "Jani" == Jani Tolonen <jani@stripped> writes:

Jani> Hi!
>> I was wondering with the `TRUNCATE TABLE table_name' why it doesn't
>> reset the auto_increment value of the table being dropped? Shouldn't it?
>> 
>> Also, I've noticed that TRUNCATE TABLE is MUCH slower than just running
>> DROP TABLE .. ; CREATE TABLE ..
>> 
>> This is not all that critical for me given I rarely use the syntax, But
>> thought you should know.

Jani> It is not defined whether TRUNCATE TABLE should reset the
Jani> auto_increment value, it may vary between table handlers.

Jani> At least for MyISAM tables TRUNCATE TABLE should be very quick,
Jani> as it currently does the same thing as 'DELETE FROM table' without
Jani> a WHERE, which makes MySQL not even open the table before deletion,
Jani> which means it should be even faster than DROP TABLE.. CREATE TABLE.

Jani> For MyISAM table TRUNCATE (and DELETE) also resets the auto_increment
Jani> value (we might change this behavior in the future).

Jani> What MySQL version and what table type are you using?

Just a small update.

In MySQL 4.0 TRUNCATE will always re-generate the table and should
always be faster than DROP + CRETE.

We added the TRUNCATE syntax to 3.23 so that people can start using
this command already in 3.23, but in 3.23 TRUNCATE is mapped to
'DELETE FROM TABLE' so it's not as fast as TRUNCATE in 4.0 if you have
support for InnoDB tables in your binary;  The problem is that we
can't simply do a re-create with InnoDB the way we do with MyISAM, so
we added to 3.23 a hack that if InnoDB is enabled we delete the rows
one by one.

Regards,
Monty

-- 
For technical support contracts, goto https://order.mysql.com/
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Mr. Michael Widenius <monty@stripped>
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB, CTO
/_/  /_/\_, /___/\___\_\___/   Helsinki, Finland
       <___/   www.mysql.com
Thread
TRUNCATE TABLE table_nameColin Faber10 Aug
  • TRUNCATE TABLE table_nameJani Tolonen10 Aug
    • TRUNCATE TABLE table_nameMichael Widenius10 Aug
  • Re: TRUNCATE TABLE table_nameColin Faber10 Aug
    • Re: TRUNCATE TABLE table_nameMichael Widenius11 Aug