{"id":2104,"date":"2010-10-21T07:52:45","date_gmt":"2010-10-21T05:52:45","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=2104"},"modified":"2010-10-21T07:52:45","modified_gmt":"2010-10-21T05:52:45","slug":"simple-guideline-for-choosing-appropriate-innodb-primary-keys","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/simple-guideline-for-choosing-appropriate-innodb-primary-keys","title":{"rendered":"Simple guideline for choosing appropriate InnoDB PRIMARY KEYs"},"content":{"rendered":"

Risking some flames, I’d like to suggest only two options for choosing PRIMARY KEY<\/strong>s for InnoDB tables. I suggest they should cover 99% (throwing numbers around) of cases.<\/p>\n

PRIMARY KEY cases<\/h4>\n
    \n
  1. An integer (SMALLINT \/ INT \/ BIGINT), possibly AUTO_INCREMENT<\/strong> column.<\/li>\n
  2. The combination of two columns on a many-to-many connecting table (e.g. film_actor<\/strong>, which connects film<\/strong>s to actor<\/strong>s), the two columns being the PRIMARY KEY<\/strong>s of respective data tables. This rule may be extended to 3-way relation tables.<\/li>\n<\/ol>\n

    A short recap: an InnoDB must have a PRIMARY KEY<\/strong>. It will pick one if you don’t offer it. It can pick a really bad UNIQUE KEY<\/strong> (e.g. website_url(255)<\/strong>) or make one up using InnoDB internal row ids. If you don’t have a good candidate, an AUTO_INCREMENT PRIMARY KEY<\/strong> is probably the easiest way out.<\/p>\n

    A 2-column combination for a many-to-many connection table is common and viable. The PRIMARY KEY<\/strong> will not only provide with good join access method, but will also provide with the required UNIQUE<\/strong> constraint.<\/p>\n

    An integer-based PRIMARY KEY<\/strong> will make for more compact & shallow index tree structures, which leads to less I\/O and page reads.<\/p>\n

    An AUTO_INCREMENT<\/strong> will allow for ascending PRIMARY KEY<\/strong> order of INSERT<\/strong>, which is InnoDB-friendly: index pages will be more utilized, less fragmented.<\/p>\n

    Exceptions<\/h4>\n