What are the pros and cons of adding secondary columns to a table's primary key?

Question

In MySQL, I understand that with InnoDB the contents of the primary key get attached to the leaf node of every other index on the table.

I have a photos table. Queries on it come in many different forms, with many different GROUPings, WHERE filters on many columns, etc.

As such I have a plethora of indexes on the table.

One thing that holds true though, is that for almost any query on the table, I want to retrieve filename.

(I've not made the filename the PK as other tables (photo_tags etc) have FK references to the photo table, and an auto-incrementing photoId column seems a lot more efficient?)

Am I right in thinking that it'd be savvy to make the PRIMARY KEY (photoId, filename) so that filename is always present in the leaf nodes of any other index lookups?

Sorry if any of my terminology is wrong, I'm far from a DBA expert!

Answer 1

Short answer: The filename in PRIMARY KEY (photoId, filename) buys you nothing (if I correctly understand your use case).

Long answer (and a lot more tips):

• AUTO_INCREMENT needs the id to be the first column in some index; nothing more.
• The PRIMARY KEY is, but definition, UNIQUE. Adding extra columns changes that uniqueness constraint. This is likely to hurt in that you can accidentally insert rows that would violate the previous (shorter) PK.
• As far as the disk space for the PK and data (which share the same BTree), there is virtually no change.
• On the other hand, the fatter the PK is, the fatter secondary indexes will be. This is because the PK is implicitly added to each secondary key (as you stated). Only sometimes does this fattening matter.
• For performance, you need to have the appropriate index. That is, design the indexes, then see if you can do some minor cleanups. Example: INDEX(a), INDEX(a,b) --> only INDEX(a,b).
• Indexes should tackle WHERE, then GROUP BY, then ORDER BY. More discussion. (Without specifics, I can't help in more detail.)
• What is retrieved (SELECT filename, ...) is rarely a consideration in designing indexes. Exception: "Covering" index.
• If the Optimizer has decided to use the PK, then adding extra columns to the PK will not help at all. Adding columns to a secondary index may matter.
• It's an old wive's tale that you should avoid varchars for ints in the PK. It is sometimes beneficial to some degree. I would rather discuss specific cases. A BTree works the same way whether it is ordered by an INT or VARCHAR(99).
• We need to see the main queries against the table in order to be more specific.

In one application (and using your photo analogy): The giant Photos table had an auto_inc photoID, but most queries included WHERE userID = 123. So:

PRIMARY KEY(photoID),
INDEX(..., userID, ...),
INDEX(..., userID, ...),
INDEX(..., userID, ...),
...

Since the data is ordered via the PK, looking up by userID led to jumping around on the disk. This turned out to be almost twice as fast:

PRIMARY KEY(userID, photoID) -- cluster on userid; photoid for uniqueness
INDEX(photoID),   -- kept auto_inc happy
INDEX(..., userID, ...),
INDEX(..., userID, ...),
...   -- fewer were needed

The uniqueness constraint on photoID was lost, but the INSERTs used auto_inc to effectively never violate the constraint.

This table was much too big to be cached in RAM; the speedup mostly came from clustering on userID.

To loop back to your Question... That suggested change hurts this query:

SELECT filename FROM Photos WHERE photoID = 235;  (with no other columns)

The simple answer is to change INDEX(photoID) into a 'covering' index: INDEX(photoID, filename). Note that this does not contradict anything I said above.