3

I am creating an Innodb table with four columns.

Table

 column_a (tiny_int)
 column_b (medium_int)
 column_c (timestamp)
 column_d (medium_int)

 Primary Key -> column_a, column_b, column_c

From a logical standpoint, columns A, B, C must be made into a PK together.However, to increase performance and be able to read directly from the index (using index) I am considering a PK that comprises of all 4 columns (A, B, C, D).

QUESTION

What would the performance be of appending an additional column to the Primary Key on an Innodb table?

CONSIDERATIONS

  • Surrogate primary keys are absolutely out of the question
  • No other indexes will exist on this table
  • Table is read/write intensive (both about equal)

Thank you!

ProfileTwist
  • 1,524
  • 1
  • 13
  • 18
  • 2
    Adding `column_d` to your PK will mean that one can end up with multiple records with identical values in `(column_a, column_b, column_c)` so long as `column_d` differs. Also, why resist adding other indexes to the table: if you want a covering index, why not add one *in addition to* the PK? Remember, **premature optimization is the root of all evil** - are you actually faced with a performance problem that you're trying to solve? If so, what is it? – eggyal Dec 29 '12 at 14:12
  • Very good point with `column_d` and I asked wondering if there was a rule of thumb or some guiding principle. There are quite a few tables with large PKs and I was hoping for guidance. Thank you – ProfileTwist Dec 29 '12 at 14:22

2 Answers2

1

In InnoDB, the PRIMARY KEY index structure includes all non-key fields and will automatically use them for covering index queries and row elimination. There is no separate "data" structure other than the PRIMARY KEY index structure. It is not necessary to add additional fields to the PRIMARY KEY definition itself. Note that it won't show Using index when it's using the PRIMARY KEY on an InnoDB table, because it's a different code path which doesn't trigger the addition of that message.

jeremycole
  • 2,741
  • 12
  • 15
0

A few things to consider:

  1. Unless the query in question uses all of the columns in the index, the index will not be used.
  2. As jeremycole notes: in the Innodb structure all row data is stored in the B-tree leaf nodes of the clustered index (PRIMARY INDEX)

This concept is covered: http://www.innodb.com/wp/wp-content/uploads/2009/05/innodb-file-formats-and-source-code-structure.pdf http://blog.johnjosephbachir.org/2006/10/22/everything-you-need-to-know-about-designing-mysql-innodb-primary-keys/

... and in jeremy's blog post here: http://blog.jcole.us/2013/01/07/the-physical-structure-of-innodb-index-pages/

As such, a query on A, B, C will be sufficient for efficiently obtaining all values on this Innodb table.

Drew
  • 6,311
  • 4
  • 44
  • 44
  • 1
    I'm sorry, but this answer is just incorrect. The `PRIMARY KEY` index structure in InnoDB includes all non-key fields as its "value", meaning that there is no separate "data" structure (as is the case with e.g. MyISAM `.MYD` files). Creating a separate covering index for this case will do more harm than good. – jeremycole Jan 08 '13 at 16:57
  • jeremy is absolutely correct, about my earlier incorrect answer. – Drew Jan 08 '13 at 20:26