1

I have a VARCHAR(255) primary field field in an InnoDB database table. I only store values of length 32 bytes in it (32 ASCII characters). My primary key has a prefix length of 767 bytes. Clearly, this longer prefix length than I need. Does this have any performance impact?

2

If it is always 32 ascii bytes, then say CHAR(32) CHARACTER SET ascii.

There are subtle reasons for not making the field bigger than necessary. They mostly involve how temporary tables are generated in complex SELECTs. Most importantly, the tmp table is MEMORY and turns VARCHAR(255) into CHAR(255) of the given charset. If that is utf8, then you are unnecessarily using 755-32 extra bytes for each row in the tmp table. This can lead to slowdowns.

"32" smells like hex for something? UNHEX(...) and put it into BINARY(16) to make the PK even smaller.

Every secondary key (in InnoDB) implicitly includes a copy of the PK. Making the PK bigger than necessary can impact disk space.

| improve this answer | |

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