MySQL::Error: Specified Key Was Too Long; Max Key Length Is 1000 Bytes

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

As @Devart says, the total length of your index is too long.

The short answer is that you shouldn't be indexing such long VARCHAR columns anyway, because the index will be very bulky and inefficient.

The best practice is to use prefix indexes so you're only indexing a left substring of the data. Most of your data will be a lot shorter than 255 characters anyway.

You can declare a prefix length per column as you define the index. For example:

...
KEY `index` (`parent_menu_id`,`menu_link`(50),`plugin`(50),`alias`(50))
...

But what's the best prefix length for a given column? Here's a method to find out:

SELECT
 ROUND(SUM(LENGTH(`menu_link`)<10)*100/COUNT(`menu_link`),2) AS pct_length_10,
 ROUND(SUM(LENGTH(`menu_link`)<20)*100/COUNT(`menu_link`),2) AS pct_length_20,
 ROUND(SUM(LENGTH(`menu_link`)<50)*100/COUNT(`menu_link`),2) AS pct_length_50,
 ROUND(SUM(LENGTH(`menu_link`)<100)*100/COUNT(`menu_link`),2) AS pct_length_100
FROM `pds_core_menu_items`;

It tells you the proportion of rows that have no more than a given string length in the menu_link column. You might see output like this:

+---------------+---------------+---------------+----------------+
| pct_length_10 | pct_length_20 | pct_length_50 | pct_length_100 |
+---------------+---------------+---------------+----------------+
|         21.78 |         80.20 |        100.00 |         100.00 |
+---------------+---------------+---------------+----------------+

This tells you that 80% of your strings are less than 20 characters, and all of your strings are less than 50 characters. So there's no need to index more than a prefix length of 50, and certainly no need to index the full length of 255 characters.

PS: The INT(1) and INT(32) data types indicates another misunderstanding about MySQL. The numeric argument has no effect related to storage or the range of values allowed for the column. INT is always 4 bytes, and it always allows values from -2147483648 to 2147483647. The numeric argument is about padding values during display, which has no effect unless you use the ZEROFILL option.

Mysql::Error: Specified key was too long; max key length is 1000 bytes

This is solely a MySQL issue -

MySQL has different engines - MyISAM, InnoDB, Memory...

MySQL has different limits on the amount of space you can use to define indexes on column(s) - for MyISAM it's 1,000 bytes; it's 767 for InnoDB. And the data type of those columns matters - for VARCHAR, it's 3x so an index on a VARCHAR(100) will take 300 of those bytes (because 100 characters * 3 = 300).

To accommodate some indexing when you hit the ceiling value, you can define the index with regard to portions of the column data type:

CREATE INDEX example_idx ON YOUR_TABLE(your_column(50))

Assuming that your_column is VARCHAR(100), the index in the example above will only be on the first 50 characters. Searching for data beyond the 50th character will not be able to use the index.

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

767 bytes in MySQL version 5.6 (and prior versions), is the stated prefix limitation for InnoDB tables. It's 1,000 bytes long for MyISAM tables. This limit has been increased to 3072 bytes In MySQL version 5.7 (and upwards).

You also have to be aware that if you set an index on a big char or varchar field which is utf8mb4 encoded, you have to divide the max index prefix length of 767 bytes (or 3072 bytes) by 4 resulting in 191. This is because the maximum length of a utf8mb4 character is four bytes. For a utf8 character it would be three bytes resulting in max index prefix length of 255 (or minus null-terminator, 254 characters).

One option you have is to just place lower limit on your VARCHAR fields.

Another option (according to the response to this issue) is to get the subset of the column rather than the entire amount, i.e.:

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

Tweak as you need to get the key to apply, but I wonder if it would be worth it to review your data model regarding this entity to see if there's improvements possible, which would allow you to implement the intended business rules without hitting the MySQL limitation.

How to resolve specified key was too long max key length is 255 bytes in mysql?

EDIT

Issue appears to be related to the TINYTEXT datatype. (I can replicate the observed behavior with MySQL version 5.7.17-0ubuntu0.16.04.1-log, using either InnoDB or MyISAM.)

The short answer (as a workaround, how to resolve the 1071 warning) is to use datatype VARCHAR(255) in place of TINYTEXT.

---

I ran several test cases with various character sets (utf8, utf8mb4, latin1) and using InnoDB and MyISAM storage engines. The 1071 warning appears to be related to the prefix length specified in the index on the TINYTEXT column... appears to be a MySQL limit on the prefix length (not specifically related to InnoDB, since I can replicate the behavior with MyISAM.) I did not test with any other TEXT types other than TINYTEXT.

---

PREVIOUS ANSWER

Index key length limit for InnoDB tables is 767 bytes.

The name(255) in the key definition is specifying the first 255 characters of name. With the MySQL utf8 characterset, a character can take from one to three bytes. And 255 times three is 765. Add in the four bytes for the int private_id, and that's 769, which exceeds the maximum.

That's why you are getting the error.

Several approaches to resolving that.

Easiest would be to reduce the number of characters of name that are included in the index, e.g.

UNIQUE KEY `name` (`private_id`,`name`(254))

If that doesn't satisfy your use case, then you might need to consider using the deprecated innodb_large_prefix setting. You would need to use DYNAMIC or COMPRESSED row format. See the discussions here:

https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format-specification.html

How to create this table? #1071 - Specified key was too long; max key length is 1000 bytes

PRIMARY KEY (realurl,catagory) has a size of (200 + 200) * 3 = 1,200 bytes, which is greater than the 1,000 byte limit, as MySQL stores utf8 encoded chars as 3 bytes.

You'll need to reduce the size of the the fields that make up the primary key or you can upgrade MySQL version to the latest release.

Also see this other question: Error: Specified key was too long; max key length is 1000 bytes.

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

Because your primary key is a multi-column primary key on both UTF8 VARCHAR(255) columns, the index size is the size of both columns, added together.

Indexes on UTF8 columns automatically allocate all possible space, where each character could take up to 3 bytes. Therefore, your index size is 255 characters * 3 bytes * 2 columns = 1530 bytes.

MyISAM has a limit of 1000 bytes for indexes. InnoDB has an even smaller limit (767 bytes) unless you're on MySQL 5.7.7+, in which case the limit is 3072 bytes by default.

Try to reduce your index size. Typically, the smaller your index size, the better your seek performance will be.

---

Related Topics