1

Sorry if this is a FAQ, but I'm having problems turning up exactly what I'm asking:

I have a table (mysql) with a primary key which is composed of multiple columns only because this is necessary to make the PK unique (won't be using an autoincrement column).

However, most lookups will be against just the first column in the primary key.

So should I have a separate index only containing that single column, or is the multi-column PK sufficient (at least as long as the query column is listed first in the index definition)?

1

In short, it is sufficient. As far as the searched field is the first in a composite index (PK, unique, or regular index), no need for other separate index.

However, you mentioned that you

(won't be using an autoincrement column)

Is there a reason for that? Composite PK might have performance hit, specially if you are using InnoDB engine, and if you have many updates on the table (insert/update/delete)

  • The table will be extremely large (possibly high hundreds of millions of rows), didn't want to rely on autoincrement so large. Is that a mistake? Inserts are VERY frequent, lookups not so much, those normally pull all records based on the first column in the PK. Other thoughts greatly appreciated! – chell Sep 10 '15 at 1:09
  • In this case, I strongly recommend having an auto increment ID as a PK. If you are concerned about the limit of the integer type, define it as id BIGINT UNSIGNED AUTO_INREMENT. This will be 8 bytes field, which has a max value of 2^64 (18446744073709551615). Note here please that if you are within hundreds of millions, you may use unsigned integer to save 4 bytes per row. – Jehad Keriaki Sep 10 '15 at 1:14
  • The reason being that it's faster to insert and not have to update an index containing so many columns? BTW, yes it's InnoDB – chell Sep 10 '15 at 1:17
  • In InnoDB primary key is a clustering index too, your 3 columns are probably not guaranted to come in ascending order as autoincrement is, so inserts can and will happen randomly "in the middle" of the primary key instead of appending to the end when you use your primary. – jkavalik Sep 10 '15 at 5:10
  • I agree with Jehad that the index is sufficient for your queries but I disagree about the autoincrement. For the specific case, I don't see any reason for having an auto-incrementing key. But this is a slightly different discussion. @chell, perhaps you should add a new question, with all the details (CREATE TABLE, number of inserts per second, etc) – ypercubeᵀᴹ Sep 10 '15 at 8:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.