{"id":3196,"date":"2011-03-22T08:31:18","date_gmt":"2011-03-22T06:31:18","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=3196"},"modified":"2011-03-22T08:31:18","modified_gmt":"2011-03-22T06:31:18","slug":"reasons-to-use-auto_increment-columns-on-innodb","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/reasons-to-use-auto_increment-columns-on-innodb","title":{"rendered":"Reasons to use AUTO_INCREMENT columns on InnoDB"},"content":{"rendered":"<p>An InnoDB table must have a primary key (one is created if you don&#8217;t do it yourself). You may have a <a href=\"http:\/\/en.wikipedia.org\/wiki\/Natural_key\">natural key<\/a> at hand. Stop! Allow me to suggest an AUTO_INCREMENT may be better.<\/p>\n<p>Why should one add an AUTO_INCREMENT PRIMARY KEY on a table on which there&#8217;s a natural key? Isn&#8217;t an AUTO_INCREMENT a pseudo key, meaning, it doesn&#8217;t have any explicit relation to the row data, other than it is a number and unique?<\/p>\n<p>Yes, indeed so. Nevertheless, consider:<\/p>\n<ul>\n<li>Natural keys are many times multi-columned.<\/li>\n<li>Multi column PRIMARY KEYs make for larger keys, and make for bloated secondary keys as well. You may be wasting space for storing the additional AUTO_INCREMENT column, but you may gain space back on secondary keys.<\/li>\n<li>Multi column PRIMARY KEYs make for more locks. See also <a href=\"http:\/\/code.openark.org\/blog\/mysql\/reducing-locks-by-narrowing-primary-key\">this post<\/a>.<\/li>\n<li>InnoDB INSERTs work considerably faster when worked in ascending PRIMARY KEY order. Can you ensure your natural key is in such order?<\/li>\n<li>Even though an AUTO_INCREMENT makes for an INSERT bottleneck (values must be given serially), it is in particular helpful to InnoDB by ensuring PRIMARY KEY values are in ascending order.<\/li>\n<li>AUTO_INCEMENT makes for chronological resolution. You <em>know<\/em> what came first, and what came next.<\/li>\n<li>In many datasets, more recent entries are often being accessed more, and are therefore &#8220;hotter&#8221;. By using AUTO_INCREMENT, you&#8217;re ensuring that recent entries are grouped together within the B+ Tree. This means less random I\/O when looking for recent data.<\/li>\n<li>A numerical key is in particular helpful in splitting your table (and tasks on your table) into smaller chunks. I write <a href=\"http:\/\/code.google.com\/p\/openarkkit\/\">tools<\/a> which can work out with any PRIMARY KEY combination, but it&#8217;s easier to work with numbers.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>An InnoDB table must have a primary key (one is created if you don&#8217;t do it yourself). You may have a natural key at hand. Stop! Allow me to suggest an AUTO_INCREMENT may be better. Why should one add an AUTO_INCREMENT PRIMARY KEY on a table on which there&#8217;s a natural key? Isn&#8217;t an AUTO_INCREMENT [&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":[26,14,17],"class_list":["post-3196","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-indexing","tag-innodb","tag-schema"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-Py","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3196","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=3196"}],"version-history":[{"count":19,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3196\/revisions"}],"predecessor-version":[{"id":3407,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3196\/revisions\/3407"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=3196"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=3196"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=3196"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}