[TYPO3-dev] How standard compliant is "truncate"?

[ries van Twisk]

On Feb 5, 2009, at 9:39 AM, Dmitry Dulepov wrote:

> Hi!
>
> Martin Kutschker wrote:
>> Is truncate really faster?  I assumed that a DELETE without a WHERE
>> clause will work internally the same as a TRUNCATE.
>>
>> BTW, is there any difference for MyIsam or InnoDB tables?
>
> Yes. TRUNCATE on InnoDB is an alias for DELETE and deletes all rows  
> inside one internal transaction.

Dmitry,

as stated before (please read) and according to the MySQL manual:

For an InnoDB table before version 5.0.3, InnoDB processes TRUNCATE  
TABLE by deleting rows one by one. As of MySQL 5.0.3, row by row  
deletion is used only if there are any FOREIGN KEY constraints that  
reference the table. If there are no FOREIGN KEY constraints, InnoDB  
performs fast truncation by dropping the original table and creating  
an empty one with the same definition, which is much faster than  
deleting rows one by one

>
>
> TRUNCATE on MyISAM is much faster than delete because it just resets  
> row counter to 0. DELETE removes all rows and much slower on MyISAM  
> with many rows.