3 ms·

Fascinating! But it replaces one question with another: > The way innodb MySQL databases worked was that you can only have 767 bytes for an index Was somebody

by Twisol 5y ago

Fascinating! But it replaces one question with another: > The way innodb MySQL databases worked was that you can only have 767 bytes for an index Was somebody a Boeing fan or is there another story behind the 767 figure? EDIT: aaand I figured it out. 256 * 3 is 768, then take away one for the null terminator. Wild.

dmux 5y ago
Additional information/investigation I found: https://dba.stackexchange.com/questions/57005/why-are-innodbs-index-keys-limited-to-767-bytes https://dba.stackexchange.com/questions/57005/why-are-innodb...
masklinn 5y ago
> EDIT: aaand I figured it out. 256 * 3 is 768, then take away one for the null terminator. Wild. That's the consequence not the cause. The cause is > 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. So some ancient MySQL probably used 4KB page which only had spacer for 768 bytes index records (probably because of the page structure). When the page size was increased, the key length was likely kept. Incidentally, Postgres' default (BTree) index has a higher-but-not-infinite limit of 2730 bytes. It can be surprising when you index a varchar column without restricting column length and one day inserts start blowing up because someone tried inserting 5k worth of crap in the column.
- Twisol 5y ago
  Nice extra context, thanks! You've now introduced two more magic constants, though, and so the question continues: where do those come from? Since 3072 is 3 * (16KB / 16), I'm guessing the 3 still comes from the original article's `Latin1` 3-byte encoding origin story. The 1/16th sounds like a balancing factor between the width of an index element and the number of elements per page.

by Twisol 5y ago

dmux 5y ago

Additional information/investigation I found: https://dba.stackexchange.com/questions/57005/why-are-innodbs-index-keys-limited-to-767-bytes https://dba.stackexchange.com/questions/57005/why-are-innodb...

masklinn 5y ago

> EDIT: aaand I figured it out. 256 * 3 is 768, then take away one for the null terminator. Wild. That's the consequence not the cause. The cause is > 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. So some ancient MySQL probably used 4KB page which only had spacer for 768 bytes index records (probably because of the page structure). When the page size was increased, the key length was likely kept. Incidentally, Postgres' default (BTree) index has a higher-but-not-infinite limit of 2730 bytes. It can be surprising when you index a varchar column without restricting column length and one day inserts start blowing up because someone tried inserting 5k worth of crap in the column.

Twisol 5y ago
Nice extra context, thanks! You've now introduced two more magic constants, though, and so the question continues: where do those come from? Since 3072 is 3 * (16KB / 16), I'm guessing the 3 still comes from the original article's `Latin1` 3-byte encoding origin story. The 1/16th sounds like a balancing factor between the width of an index element and the number of elements per page.