3

MySQL's official documentation states about InnoDB:

An index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix.

The meaning of this statement is clear to me, however I do not understand the reasons of this limitation, which seems arbitrary (why 767?).
Any pointers?

Improve this question
9
  • Why some other number?
    – Mihai
    Commented Jan 17, 2014 at 15:30
  • ...or, why not some other number?
    – LoicAG
    Commented Jan 17, 2014 at 15:31
  • 3
    My guess: 767 = 3*255 + 2. You can have a column indexed if it is up to VARCHAR(255) (when it's using UTF-8).
    – ypercubeᵀᴹ
    Commented Jan 17, 2014 at 15:36
  • 3
    @ypercube's calculation is correct. LoicAG, your are correct to some extent that "that's a consequence of the limitation, not a reason for it". But that's the world of software engineering: you set some limitations (eg one byte for the length of a text) an other limitations are derived from your decisions.
    – Shlomi Noach
    Commented Jan 17, 2014 at 18:52
  • 2
    Varchar columns use extra bytes to store the length of the strings (in bytes). So, for 765 (which is >=2^8 and < (2^8)^2 you need 2 more bytes. Now, why 255 was chosen as a reasonable size, I have not a clue.
    – ypercubeᵀᴹ
    Commented Jan 20, 2014 at 15:22

1 Answer 1

Reset to default
3

This seems to be linked to the size of the InnoDB pages:

If you reduce the InnoDB page size to 8KB or 4KB by specifying the innodb_page_size option when creating the MySQL instance, the maximum length of the index key is lowered proportionally, based on the limit of 3072 bytes for a 16KB page size. That is, the maximum index key length is 1536 bytes when the page size is 8KB, and 768 bytes when the page size is 4KB.

Thanks to XL on the #mysql IRC chat for the pointer to page sizes. I will update this answer when I find out more about the InnoDB page format and its limitations.

UPDATE
The quote above applies to the total index key length for a row, whereas my original question was about the index key length for a single-column index. My guess at this point is that the 767-byte limitation is legacy from the time when all pages were 4KB in size, and thus both single-column and multi-column indexes were limited to a total length of 767 (+1?) bytes.
I will update this question again when I have more information about:

  • how the page size influences the index key length,
  • why there is a 1-byte difference in maximum key length between single-column indexes and multi-column indexes, and
  • why the legacy value of 767 bytes (if it is indeed a legacy value) was kept for single-column index key lengths.
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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