The 191 Problem

Someone adds an index to a VARCHAR(255) column and MySQL answers with ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes. They search the error text. The top answer, copied forward through a decade of forum threads and now through generated documentation, is one line: change the column to VARCHAR(191).

Both numbers are correct. Neither is a constant of MySQL. 191 is 767 divided by 4, rounded down, because the widest character that utf8mb4 can store occupies four bytes. And 255, the number the developer typed without thinking, is not a limit either. It is the largest latin1 length that still costs a single length byte on every row.

That is the whole problem in one sentence: column lengths are declared in characters and enforced in bytes, and the exchange rate between them is not one. Once you see the division, four numbers that look arbitrary stop being arbitrary. More usefully, you can compute the right number for any table instead of memorising someone else's.

1. Two Numbers, Two Questions

MySQL's manual is unusually direct about the first half of this. A CHAR or VARCHAR column is declared with a length that indicates "the maximum number of characters you want to store". The characters, not the bytes. Then, two paragraphs later, storage is described in bytes: "The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes."

So a single declaration, VARCHAR(255), is simultaneously a promise about characters and a constraint about bytes, and nothing in the syntax says which character set will be standing behind it. The same token in a schema becomes a 256-byte column under latin1 and a 1,022-byte column under utf8mb4. The difference is not a tuning parameter. It is a factor of four.

Every "magic" number in this corner of the industry is a quotient of that form. The table below is the entire subject of this briefing, compressed.

Read that table as a caution about how technical folklore forms. The number 191 is not wrong. It is the correct answer to a question about a row format most current installations do not use, and it has been detached from that question and repeated as though it were a property of the type.

2. One Byte, Sometimes

UTF-8 is a variable-width encoding, and the width depends on the code point. The ladder has four rungs and no others; RFC 3629 closed off the five- and six-byte forms that the original specification permitted.

Only the first rung is one byte, and it contains 128 code points out of the 1,114,112 that Unicode assigns. That is 0.0115% of the code space. The claim that "UTF-8 uses one byte per character" is a statement about the 0.0115%, and it is repeated as though it were a statement about text.

The practical consequence is that the byte cost of a column is a property of the language in it, not of the schema. Measured on sample sentences in each script, with spaces counted as the single byte they are:

Chinese and Japanese land exactly on 3.00 because every character in those sentences sits in the three-byte rung and the spaces are a rounding effect on a twenty-character sample. Emoji land exactly on 4.00 because every one of them is above U+FFFF. English sits at 1.00 by definition.

This is where capacity estimates go wrong in a way that survives review. A team sizing a table for 20 million rows of user-entered text will multiply by a width they measured on test data, and test data is almost always English. Change the user base, and the same schema holds four times the bytes with no migration, no alert, and no change to the declaration.

3. Four Things Called a Character

Before any of this arithmetic is meaningful you have to know which count you are holding, because "character" names at least four different quantities and they disagree by up to 25×.

The last row is one character to a reader, seven code points to a Unicode-aware language, eleven units to JavaScript, and twenty-five bytes to the disk. If a product requirement says "limit the name field to 25 characters", the four interpretations produce fields that differ in capacity by a factor of 25, and only one of them will match what the reader expects.

The concrete breakage is well known to anyone who has written a front-end validator. In JavaScript, "👍".length is 2, because String.length counts UTF-16 code units and every code point above U+FFFF becomes a surrogate pair. Array.from("👍").length is 1. In Python, len("👍") is 1, because Python 3 strings are sequences of code points. Neither language gives you graphemes until you ask for them by name, which is Intl.Segmenter in JavaScript and a third-party library in Python.

MySQL sits at the code-point level for CHAR_LENGTH(), and at the byte level for LENGTH(). That pair is the most useful instrument in this whole article, and section eight turns it into a single diagnostic query.

4. The Step at 63

Here is a cost that no declaration makes visible. A VARCHAR value is stored as a length prefix followed by the bytes. The prefix is one byte when the value can never exceed 255 bytes and two bytes otherwise. The word that matters is can: MySQL decides at definition time, using the maximum the column could hold, not the length of any particular row.

So the prefix doubles at a different character count for every character set, and the threshold is simply 255 divided by the widest character the set permits.

VARCHAR(63) under utf8mb4 can hold 252 bytes, so it costs one prefix byte. VARCHAR(64) can hold 256, so it costs two. One additional character in the declaration adds one byte to every row in the table, permanently, and the row does not have to use that character.

At ten million rows that is ten megabytes, which is not a crisis. The reason to know it is not the ten megabytes. It is that this is where 255 came from in the first place. Under latin1, VARCHAR(255) is exactly the largest column that still carries a one-byte prefix, and that is why the idiom spread in the years when latin1 was the default. It then survived the migration to three- and four-byte character sets, where it no longer marks any boundary at all.

5. Where 767 Comes From

The 767-byte figure is not a property of VARCHAR. It is a property of two InnoDB row formats. MySQL's manual states it plainly: "The index key prefix length limit is 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format. The index key prefix length limit is 767 bytes for InnoDB tables that use REDUNDANT or COMPACT row format."

It then does the division for you, and this is the sentence that half the internet is paraphrasing without attribution: "you might hit this limit with a column prefix index of more than 191 characters on a TEXT or VARCHAR column, assuming a utf8mb4 character set and the maximum of 4 bytes for each character."

Look at the second column of the first row. 767 ÷ 3 is 255.67, which rounds down to 255. That is the second reason the number 255 became the default length for indexed string columns in MySQL schemas: under the three-byte character set, it was simultaneously the largest one-prefix-byte length and the largest fully indexable length. Two unrelated constraints agreed on the same value, and the agreement made the choice look like a rule.

Then utf8mb4 arrived, the maximum width went from three to four, both constraints moved, and only one of them moved in a way anyone noticed. The index limit produced 191 and a famous error message. The prefix limit produced 63, and nobody wrote a blog post about it.

6. Why 191 Is Usually Wrong Now

Here is the part that the copied answers omit. The default InnoDB row format is DYNAMIC, and under DYNAMIC the key prefix limit is 3,072 bytes. A VARCHAR(255) column under utf8mb4 can occupy at most 1,020 bytes, which is a third of the budget. It indexes without truncation, and the error that started this article does not occur.

So on a schema you are creating today, VARCHAR(191) is a fix for a condition you would have to opt into. Writing it by reflex costs you 64 characters of capacity in exchange for nothing, and it makes the schema harder to reason about, because the next person has to work out which of the two questions the 191 was answering.

There is a broader pattern here, and it is the same one that makes old performance advice dangerous. A number is derived from a constraint, the constraint changes, and the number keeps circulating because it is easier to copy than to re-derive. The fix outlives the bug. In this case the bug was a 767-byte limit, the fix was a shorter column, and the limit is now four times larger while the column stayed short.

7. SQL Server Says 450

If the division is real, other databases should produce their own magic numbers from their own limits. They do. SQL Server's index key ceiling is 900 bytes for a clustered index and 1,700 for a nonclustered one, and nvarchar stores two bytes per character, so the largest fully indexable declaration is 450 characters, or 850 for a nonclustered index.

That 450 is the exact analogue of MySQL's 191. It is the same quotient of an index byte limit and a character width, and it is subject to the same drift: whether it applies depends on the index type and on how a particular column is used.

PostgreSQL is the interesting row, because it refuses the premise. Its manual says both varchar(n) and char(n) "can store strings up to n characters (not bytes) in length", and that there is "no performance difference among these three types" — the third being text, which has no length limit at all. There is no magic number for Postgres because there is no per-column byte prefix to divide.

The storage details differ too, in a way that is worth knowing when you port a schema. Postgres charges one byte of overhead for a string of up to 126 bytes and four bytes above that, and it compresses long values automatically. Whether you write varchar(255) or text changes the constraint, not the disk.

The lesson is not "Postgres is better". It is that the number 191 encodes an implementation detail of one engine's index format, and has been exported to other engines as though it were a property of text.

8. Two Ledgers, Not One

Now the part that determines whether your storage estimate holds. A declared width is charged against two different budgets, and only one of them is about bytes on disk.

The first ledger is DDL-time. MySQL imposes a 65,535-byte limit on the combined maximum size of all columns, and it charges that budget using maximum possible widths, not actual values. A VARCHAR(255) under utf8mb4 reserves 1,020 bytes plus a 2-byte prefix, which is 1,022. That means a table can hold at most 64 such columns, even if every row in it stores a single character.

The second ledger is on disk. Rows store actual values, so a VARCHAR(255) holding ten ASCII characters occupies twelve bytes: ten of data and a two-byte length prefix that the declaration forced, because 255 four-byte characters can exceed 255 bytes. Ten million such rows are 120 megabytes, not 10 gigabytes. The 1,020-byte maximum never materialises; only the width you actually use does, plus the single extra prefix byte that VARCHAR(255) charges over VARCHAR(63) and anything shorter. This is the half of the story that the "VARCHAR is expensive" folklore gets wrong.

Which means the honest answer to "how much does VARCHAR(255) cost" depends entirely on what is in it, and the declaration cannot tell you. If the column genuinely holds 255 Chinese characters, ten million rows occupy 10.22 gigabytes against the 2.55 gigabytes a naive 255-bytes-per-row estimate predicts. The estimate is wrong by 4.01×, and it is wrong because it treated a character budget as a byte budget.

Two aggregate functions settle it for any column you already have. LENGTH() returns bytes and CHAR_LENGTH() returns characters, so their ratio is the byte width of your actual data:

A result near 1.00 means your text is ASCII and every character-based estimate you have made is correct. A result near 3.00 means every row is three times the bytes you budgeted, and any capacity model built on character counts is short by that factor. The query costs one scan and answers a question that schema review cannot.

9. Cutting at a Byte Boundary

The moment a byte limit meets a character, someone truncates in the middle of one. UTF-8 is self-synchronising by design, but only from the start of a sequence. Cut at an arbitrary byte and the result may end midway through a multi-byte sequence.

Take the string ab中文. In UTF-8 it is 61 62 e4 b8 ad e6 96 87, eight bytes. Cut at three bytes and you have 61 62 e4, which ends at the first byte of a three-byte sequence. That is not a prefix of any valid UTF-8 string. A strict decoder raises an error; a lenient one substitutes U+FFFD, and the character becomes a diamond with a question mark in it.

Where this happens in practice is rarely a database. MySQL truncates a too-long value to the column limit and warns, and it truncates on character boundaries, so you get silent data loss rather than an invalid string. The invalid-string version appears one layer out: a log pipeline that cuts a message at 4,096 bytes, an object key truncated to a byte limit, an email header folded at 78 octets, a fixed-width export padded to a byte count. Each of those is a byte operation applied to a character stream.

The tell is not an error. It is a single U+FFFD, or a run of them, in a field that was fine before it passed through one more system.

10. Same Text, Different Bytes

There is a further wrinkle that survives all of the above, because it is not about how a character is encoded but about which character it is.

The letter é has two canonical encodings. It can be a single code point, U+00E9, which is two bytes in UTF-8. Or it can be e followed by the combining acute accent U+0301, which is three bytes. To a reader they are the same text. To Unicode they are canonically equivalent: normalising the second form to NFC produces the first, and the two compare equal after normalisation. To a byte counter they differ by 50%.

The consequence for schema design is narrow but real. A byte-based length limit is a limit on the encoding, so a field that holds 40 accented characters in NFD form needs 120 bytes where the NFC form needs 80. A validation rule tuned on one corpus accepts the other and rejects the first. And a uniqueness constraint compares the strings, not their normal forms, unless the collation applies normalisation, so the same visible text can pass validation twice.

The remedy is boring and cheap: normalise to NFC at the boundary where text enters your system, before you count, validate, or store it. Every language has the function. Almost nobody calls it, because the bug it prevents looks like a mysterious duplicate rather than a character encoding problem.

11. What to Declare

None of this argues for a particular number. It argues for computing one. The rules that follow from the arithmetic are short enough to apply in a review.

• Declare the width the data needs, not the width you have seen before. VARCHAR(255) marks no boundary under utf8mb4; it is a habit inherited from latin1.
• For an indexed column, divide. Take the key prefix limit of your row format, divide by the maximum bytes per character of your character set, round down. That is the largest fully indexable declaration, and it is 191 or 768 on MySQL, 450 or 850 on SQL Server.
• Do not write 191 unless the row format calls for it. On COMPACT or REDUNDANT it is required. On the default DYNAMIC it is a 25% capacity cut for a limit that does not apply.
• Measure before sizing. AVG(LENGTH()), MAX(LENGTH()), and their ratio to CHAR_LENGTH(). The ratio is the only number that tells you what a character costs in your data.
• Count in the unit the requirement uses. If the product says characters, decide whether it means graphemes, code points, or UTF-16 units before you write the validator, because the three differ by up to 25×.
• Normalise to NFC on input. It removes the last class of case where two identical-looking strings occupy different byte counts.

One counter-intuitive consequence is worth stating outright, because it cuts against common practice: a generous VARCHAR and TEXT are not automatically the safe choice. On MySQL a TEXT column cannot be indexed without an explicit prefix length, which is a real constraint that a wide VARCHAR does not have. On Postgres, by contrast, text is the native type and carries no penalty at all. The right answer is engine-specific because the limit being divided is engine-specific.

12. The Same Division Elsewhere

Column definitions are not the only place a byte limit is applied to character data. The same division shows up in three other systems that a typical application touches on every request, and the arithmetic is identical.

S3 object keys. AWS documents the limit as "a maximum length of 1,024 bytes or approximately 1,024 Latin characters", and adds the sentence that gives the game away: "In some locales, a single character may require 2 bytes for encoding." The prefix and the delimiters count against the same 1,024. Divide and the working limits are 1,024 ASCII characters, 341 Chinese or Japanese characters, or 256 emoji — and prefix depth eats into all three.

Filenames on ext4. The maximum name length is 255, and it is 255 bytes, because the name length is stored in a single 8-bit field. A filename of 85 Chinese characters reaches the ceiling, and a filename of 63 emoji reaches it too. Here the number really is bytes and there is no character budget underneath it, which is why ls can show a filename that wc -m counts as 85 characters and wc -c counts as 255.

HTTP headers and URLs. Header field values and URL paths are octet sequences, not text, and non-ASCII has to be percent-encoded. That expansion is not 2× or 3×; it is 3× on top of the UTF-8 width. A single Chinese character is three bytes, and %E4%B8%AD is nine characters, so a thousand characters of CJK title becomes nine thousand characters in a URL. Content-Length then reports the byte count of the encoded form, which is why a page whose text you measured in characters is larger on the wire than you predicted.

This is the general form of the whole article. A limit is placed in a field of fixed width, the field holds a byte count, the data is characters, and someone eventually writes the byte number down as though it were a character number. The fix is always the same division, and the only variable is which of the two numbers in the division you have forgotten.

13. Frequently Asked Questions

Is VARCHAR(255) 255 bytes?

No. The 255 is a character limit, and under utf8mb4 each character may take up to four bytes, so the maximum value is 1,020 bytes plus a two-byte length prefix, or 1,022 in total. Under latin1 the same declaration is 255 bytes plus a one-byte prefix. The two schemas are byte-identical in a migration script and differ by a factor of four on disk.

Why do people say to use VARCHAR(191)?

Because 767 ÷ 4 is 191.75, and 767 bytes is the index key prefix limit on REDUNDANT and COMPACT row formats. Rounded down, 191 four-byte characters fit. It is a quotient of an index limit and a character width, and MySQL's manual uses it as the worked example of where the limit bites. On the default DYNAMIC row format the limit is 3,072 bytes and the equivalent number is 768.

What does "max key length is 767 bytes" actually mean?

That the index key prefix, the part of a long column the B-tree stores, cannot exceed 767 bytes on that table's row format. The error is stated in bytes while the declaration is in characters, which is why the fix depends on your character set: 767 characters for latin1, 255 for utf8mb3, 191 for utf8mb4.

Why does my emoji count as two characters in JavaScript?

Because String.length counts UTF-16 code units, and every code point above U+FFFF is stored as a surrogate pair. A thumbs-up is one code point and two code units. Array.from(text).length gives code points, and Intl.Segmenter with grapheme granularity gives the clusters a reader perceives. A family emoji is one grapheme, seven code points, eleven code units, and twenty-five bytes.

Should I use utf8 or utf8mb4?

utf8mb4. In MySQL, utf8 is an alias for utf8mb3, a three-byte encoding that cannot represent anything above U+FFFF, which excludes emoji and several historic and CJK extension blocks. The only cost of utf8mb4 is that the maximum bytes per character rises from three to four, which moves the index-safe character count from 255 to 191 under the legacy row format and from 1,024 to 768 under the current one.

Does the declared VARCHAR length affect performance?

Not through the data. Rows store actual values, so a VARCHAR(255) holding ten characters costs the same as a VARCHAR(10) holding ten. The declaration affects two things: the 65,535-byte row size budget, which is charged at maximum widths and caps a table at 64 utf8mb4 columns declared VARCHAR(255), and whether an index on the column fits under the key prefix limit. Both are schema-time constraints.

14. Methodology & Disclosure

Every byte count in this article was computed rather than quoted. The UTF-8 and UTF-16 lengths in the four-unit table were produced by encoding each string with Python 3.13's standard codecs; grapheme counts were produced by a segmenter implementing the cluster rules for combining marks, variation selectors, emoji modifiers, regional indicator pairs, and ZWJ sequences. The byte-per-character figures come from single sample sentences in each script with spaces included, which is why Korean reads 2.55 rather than 3.00: its sample contains proportionally more spaces.

The division tables are integer floor operations on limits published by the vendors. MySQL's 767-byte and 3,072-byte key prefix limits, its 1-byte-or-2-byte length prefix rule, and its 65,535-byte row size limit are taken from the MySQL 8.4 Reference Manual, sections 13.3.2, 10.4.7, and 17.21. SQL Server's 900-byte and 1,700-byte index key limits are taken from Microsoft's maximum capacity specifications. PostgreSQL's character type semantics and its statement that text, varchar and char carry no performance difference are taken from the PostgreSQL 18 documentation, section 8.3. The row format limits apply to InnoDB specifically and are not a property of SQL.

Two limits below are stated as known constraints without an exact number, because the exact number is an internal page-layout parameter rather than a documented constant: the practical ceiling on a PostgreSQL b-tree index entry, and the byte boundary at which a particular logging pipeline truncates. Neither is load-bearing in the arithmetic above.

No prices appear in this briefing, so no pricing provenance applies. No vendor sponsored it, no affiliate link is present, and every number above can be reproduced from the codecs and the two divisions. Character limits are stable because Unicode is stable; index limits are not, because engines revise them, which is the entire reason this article exists.

References & Further Reading

1. Oracle. MySQL 8.4 Reference Manual, 13.3.2 The CHAR and VARCHAR Types — the declaration in characters, the 0-to-65,535 range, and the rule that a column "uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes". dev.mysql.com/doc/refman/8.4/en/char.html
2. Oracle. MySQL 8.4 Reference Manual, 17.21 InnoDB Limits — the 3,072-byte limit for DYNAMIC and COMPRESSED, the 767-byte limit for REDUNDANT and COMPACT, and the worked example of "a column prefix index of more than 191 characters … assuming a utf8mb4 character set and the maximum of 4 bytes for each character". dev.mysql.com/doc/refman/8.4/en/innodb-limits.html
3. Oracle. MySQL Reference Manual, InnoDB Row Formats — the statement that the default row format is DYNAMIC, that REDUNDANT and COMPACT support 767-byte prefixes while DYNAMIC and COMPRESSED support 3,072, and that COMPRESSED cannot be the default. dev.mysql.com/doc/refman/9.7/en/innodb-row-format.html
4. Microsoft. Maximum Capacity Specifications for SQL Server — 900 bytes per clustered index key, 1,700 bytes per nonclustered index key, 8,000 bytes per short string column, and the note that versions before 2014 supported 900 bytes for all index types. learn.microsoft.com/sql/sql-server/maximum-capacity-specifications-for-sql-server
5. The PostgreSQL Global Development Group. PostgreSQL 18 Documentation, 8.3 Character Types — that varchar(n) stores "up to n characters (not bytes)", that n cannot exceed 10,485,760, the one-byte and four-byte storage overheads, and that "there is no performance difference among these three types". postgresql.org/docs/current/datatype-character.html
6. F. Yergeau. RFC 3629: UTF-8, a transformation format of ISO 10646 — the four byte-length classes, the range U+0000 to U+10FFFF, and the prohibition on five- and six-byte sequences. rfc-editor.org/rfc/rfc3629
7. Unicode Consortium. UAX #29: Unicode Text Segmentation — the extended grapheme cluster rules that make a ZWJ family sequence one user-perceived character. unicode.org/reports/tr29
8. Unicode Consortium. UAX #15: Unicode Normalization Forms — the NFC and NFD forms and the definition of canonical equivalence that makes U+00E9 and U+0065 U+0301 the same text. unicode.org/reports/tr15
9. Related: Character Counter · GiB to GB Marketing Gap · DB Row Count to Storage · Token Size to Storage · Redis RAM to Raw JSON Ratio · Database Instance Sizing · Methodology

📜 Copyright & Attribution