How to Solve MySQL's 'Specified key too long; max key length is 1000 bytes' Error?

MySQL Error: "Specified key too long; max key length is 1000 bytes"

Issue:

You are encountering the error "Specified key was too long; max key length is 1000 bytes" when creating a MySQL table with a specific query. This query involves creating a table with multiple columns and a combined index on some of them.

Explanation:

This error occurs when the total length of the indexed columns exceeds the maximum key length allowed in MySQL, which is 1000 bytes. In this case, the "index" key spans across four columns with VARCHAR data types, each of which has a maximum length of 255 characters.

Solution:

As mentioned in the provided solution, the root cause of this error is the excessive length of the indexed columns. To resolve this, it is recommended to use prefix indexes, where only a portion of the data is indexed. This is achieved by specifying a length for each indexed column when defining the index. For instance:

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

Determining Optimal Prefix Length:

To determine the most appropriate prefix length for each column, it is recommended to analyze the data distribution. You can use a query like this:

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`;

This query will provide the percentage of rows with strings less than or equal to a certain length. Based on these results, you can choose a prefix length that covers a significant portion of the data without wasting excessive space. For example, if 80% of the strings are less than 20 characters long, a prefix length of 50 may be sufficient.

Additional Note:

It's also important to note that the INT(1) and INT(32) data types used in the original query have no impact on storage or value range. INT is always a 32-bit integer type allowing values from -2,147,483,648 to 2,147,483,647. The numeric argument in these cases primarily affects display padding and has no effect on underlying storage.

The above is the detailed content of How to Solve MySQL's 'Specified key too long; max key length is 1000 bytes' Error?. For more information, please follow other related articles on the PHP Chinese website!