Re:Why do database columns have a character length of 191?
by paulryanrogers on Monday May 17, 2021 @04:07PM
(#27187796)
I always wondered how the index prefixing impacts RTL language strings.
Re:Why do database columns have a character length of 191?
by duskwuff on Monday May 17, 2021 @04:59PM
(#27188370)
It doesn't. LTR/RTL only applies at display time -- the first character of a string is still stored first, regardless of whether it's going to show up on the left or the right side of the screen.
Re:Why do database columns have a character length of 191?
by adolph on Monday May 17, 2021 @05:07PM
(#27188464)
As I understand it, the bytes are ordered the same (without regarding big vs little endian). The RTL (or LTR) has to do with the visual representation and can be set by a nonprinting character.
https://en.wikipedia.org/wiki/Bidirectional_text
https://en.wikipedia.org/wiki/Right-to-left_mark
https://betterexplained.com/articles/understanding-big-and-l...
It is correct that InnoDB indexes used to have a per-entry limit of 767 bytes; this is however the index left prefix. It means that one can still index longer strings, but infix/suffix index access can't be performed. Prefix index access can (limited to the number of chars).
Now, if one is indexing very long strings, they should really think twice about what they're doing, as some other data structures may be more appropriate. Indexing is a balancing act, and this is not a problem of MySQL, it's a inherent problem of database systems. One may think they're going to add a 2048-bytes index and speed up access, while they may be actually slowing down the system (ie. because full table scan may be faster, and the index slows down writing and query-optimizing).
To be noted that nowadays the limit is not 767 bytes, but 3072, at least, for the default row format[¹].
¹=https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/innodb-lim...