Comments on: Simple guideline for choosing appropriate InnoDB PRIMARY KEYs https://shlomi-noach.github.io/blog/mysql/simple-guideline-for-choosing-appropriate-innodb-primary-keys Blog by Shlomi Noach Fri, 22 Oct 2010 11:15:48 +0000 hourly 1 https://wordpress.org/?v=5.3.3 By: Mark R https://shlomi-noach.github.io/blog/mysql/simple-guideline-for-choosing-appropriate-innodb-primary-keys/comment-page-1#comment-19511 Fri, 22 Oct 2010 11:15:48 +0000 https://shlomi-noach.github.io/blog/?p=2104#comment-19511 There are cases where selecting a non-obvious primary key massively improves performance, by having the rows you’re likely to be interested in clustered.

Take the case where you have some kind of Message ID which is generated in a fairly arbitrary fashion; this on its own is a valid candidate key,

However, if we add the thing that customers are likely to search on (the customer ID) as the first part of the primary key, and make it (customerID, messageID), then the customer’s messages all cluster together, in the same pages, which massively reduces the number of IOPS on these queries.

This is, of course, a special case. Typical OLTP-type apps don’t need to care about it, nor does anyone else whose database fits in memory.

Mark

]]>