1

This question already has an answer here:

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?

marked as duplicate by Evan Carroll, Colin 't Hart, Vérace, mustaccio, Max Vernon Jan 15 at 20:32

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

migrated from serverfault.com Dec 22 '15 at 19:07

This question came from our site for system and network administrators.

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.

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