Richard Clarke wrote: > > I thought truncates were supposed to be really fast. I don't really notice > any difference in speed between a truncate query and a delete query. What is > actually involved in a truncate. The docs says its faster because a drop and > recreate occurs but how is this actually faster.
In most RDBMS', there are three options for recreating a table with nothing in it: DROP and CREATE all over again. This is bad because if you have any stored procedures, all of the internal IDs that pointed to this table are invalidated and you have to recompile those SPs. This may not be the case with MySQL depending on how they end up doing binding (late or early) DELETE all from the table. This is slow because the logging system gets involved, and so the deletion of every row has to completely occur before the delete actually gets commited. In the mean time, indexes have to be updated, etc... It can be very slow. The internal IDs will remain the same, so nothing because invalidated. The performance hit here is very obvious when there are quite a lot of rows in the table. TRUNCATE. The logging system isn't involved in a truncate. The table and any associated indexes are completely dumped, and the process is unrecoverable. In this one, the internal IDs will also remain the same. -- Tom Bradford - http://www.tbradford.org Developer - dbXML - http://www.dbxml.org Maintainer - jEdit-Syntax - http://syntax.jedit.org Co-Author - O'Reilly & Associates' "Learning dbXML" --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
