{"id":1896,"date":"2010-03-09T13:37:01","date_gmt":"2010-03-09T11:37:01","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=1896"},"modified":"2010-03-09T13:37:01","modified_gmt":"2010-03-09T11:37:01","slug":"tip-faster-than-truncate","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/tip-faster-than-truncate","title":{"rendered":"Tip: faster than TRUNCATE"},"content":{"rendered":"<p><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/truncate-table.html\"><strong>TRUNCATE<\/strong><\/a> is usually a fast operation (much faster than <strong>DELETE FROM<\/strong>). But sometimes it just hangs; I&#8217;ve has several such uncheerful events with InnoDB (Plugin) tables which were extensively written to. The <strong>TRUNCATE<\/strong> hanged; nothing else would work; minutes pass.<\/p>\n<p><strong>TRUNCATE<\/strong> on tables with no <strong>FOREIGN KEY<\/strong>s <em>should<\/em> act fast: it translate to dropping the table and creating a new one (and it all depends on the MySQL version, see the manual).<\/p>\n<p>What&#8217;s faster than <strong>TRUNCATE<\/strong>, then? If you don&#8217;t have triggers nor <strong>FOREIGN KEY<\/strong>s, a <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/rename-table.html\"><strong>RENAME TABLE<\/strong><\/a> can come to the rescue. Instead of:<\/p>\n<blockquote>\n<pre>TRUNCATE log_table<\/pre>\n<\/blockquote>\n<p>Do:<\/p>\n<blockquote>\n<pre>CREATE TABLE log_table_new LIKE log_table;\r\n<strong>RENAME TABLE<\/strong> log_table TO log_table_old, log_table_new TO log_table;\r\nDROP TABLE log_table_old;<\/pre>\n<\/blockquote>\n<p>I found this to work well for me. Do note that <strong>AUTO_INCREMENT<\/strong> values can be tricky here: the &#8220;new&#8221; table is created with an <strong>AUTO_INCREMENT<\/strong> value which is immediately taken in the &#8220;working&#8221; table. If you care about not using same <strong>AUTO_INCREMENT<\/strong> values, you can:<!--more--><\/p>\n<blockquote>\n<pre>ALTER TABLE log_table_new AUTO_INCREMENT=<em>some high enough value;<\/em><\/pre>\n<\/blockquote>\n<p>Just before renaming.<\/p>\n<p>I do not have a good explanation as for why the <strong>RENAME TABLE<\/strong> succeeds to respond faster than <strong>TRUNCATE<\/strong>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>TRUNCATE is usually a fast operation (much faster than DELETE FROM). But sometimes it just hangs; I&#8217;ve has several such uncheerful events with InnoDB (Plugin) tables which were extensively written to. The TRUNCATE hanged; nothing else would work; minutes pass. TRUNCATE on tables with no FOREIGN KEYs should act fast: it translate to dropping the [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[14,21],"class_list":["post-1896","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-innodb","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-uA","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1896","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=1896"}],"version-history":[{"count":21,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1896\/revisions"}],"predecessor-version":[{"id":2131,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1896\/revisions\/2131"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=1896"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=1896"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=1896"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}