MySQL Error #1071 - Specified key was too long; max key length is 767 bytes

🔐 MySQL Error #1071 - Key Too Long

🔍 Common Issues: When working with MySQL, you may encounter the error message "#1071 - Specified key was too long; max key length is 767 bytes". This error often occurs when trying to add a unique key to a table using columns that have a combined length greater than the maximum allowed key length.

🤔 Problem Explanation: Let's take a closer look at your specific scenario. You tried to execute the following command:

ALTER TABLE `mytable` ADD UNIQUE (
    `column1`,
    `column2`
);

The error message you received was:

#1071 - Specified key was too long; max key length is 767 bytes

You mentioned that column1 has a varchar length of 20 and column2 has a varchar length of 500. Considering the encoding used (utf8_general_ci), it seems like the combined length of these two columns should be 521 bytes, which is below the 767 bytes limit. So why did you encounter the error?

💡 Solution: The key to understanding this issue lies in the encoding used by MySQL. When working with UTF-8 encoded characters, MySQL reserves more space for certain characters. The utf8_general_ci encoding allows for up to 3 bytes per character. Therefore, when calculating the total length of your columns, you need to account for the possibility of 3 bytes per character.

In your case, even though column1 has a varchar length of 20 (which should be 21 bytes), and column2 has a varchar length of 500 (which should be 501 bytes), the MySQL calculation takes into account the possibility of 3 bytes per character for each column.

Let's recalculate:

• column1 can have 20 characters, equivalent to 20 * 3 = 60 bytes (less than the limit).

• column2 can have 500 characters, equivalent to 500 * 3 = 1500 bytes (greater than the limit).

As a result, the combined length of your columns actually exceeds the maximum key length of 767 bytes.

To overcome this issue, you have a few possible solutions:

1️⃣ Solution 1: Reduce the Length of Columns If it's feasible for your use case, you can reduce the length of one or both columns (column1 and column2) so that the combined length fits within the 767 bytes limit. For example, you can change column2 to have a varchar length of 255.

2️⃣ Solution 2: Switch to a Different Encoding If reducing the length of columns is not an option, you can switch to a different character encoding that requires less space per character. For instance, you could use utf8mb4 encoding, which allows for up to 4 bytes per character. Keep in mind that this change can impact the storage requirements and performance of your database.

3️⃣ Solution 3: Use Hash Keys Instead If it's not critical to have a combined unique key for these two columns, you can consider using a hash key instead. This involves creating an additional column that holds the hash value of the concatenated values from column1 and column2. You can then create a unique index on this hash column.

🔔 Call-to-Action: If you found this blog post helpful, consider sharing it with your fellow developers who might be facing the same MySQL error. Leave a comment below if you have any questions or other MySQL-related topics you'd like us to cover.