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.
(photoId)
to reference to this table.photoId
&filename
andphotoId
is always unique (as isfilename
, but it shouldn't matter if it weren't).