0

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!

5
  • You may do it. But you must understand that it increases the size of all indexes and decreases data change speed. And the profit for non-covered indexes will be small. – Akina Sep 13 '18 at 13:38
  • I do understand that. I guess I'd like to know if it'd be considered a "best practice", or not. – Codemonkey Sep 13 '18 at 13:39
  • Additionally you will have a problem with a FKs - the referent index must be unique, so you must have additional unique index by (photoId) to reference to this table. – Akina Sep 13 '18 at 13:42
  • I'm not sure that's right @Akina, as the PK can still be UNIQUE given that it's comprised of photoId & filename and photoId is always unique (as is filename, but it shouldn't matter if it weren't). – Codemonkey Sep 13 '18 at 14:03
  • PK is unique - that's right. But this fact cannot guarantee that a part of (when you create complex PK the FK will refer to a part of it) it is unique. Only unique index can do it. – Akina Sep 13 '18 at 19:10
0

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.

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.