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

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

When creating a MySQL table with a composite index, users may encounter the error "Specified key was too long; max key length is 1000 bytes". This error indicates that the combined length of the indexed columns exceeds the maximum allowed storage size of 1000 bytes.

The issue in the provided code snippet lies in the excessive length of the index composite index:

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

Resolution

To resolve this error, consider the following:

1. Optimize Index Length:

• Long VARCHAR columns should generally not be indexed due to bulky and inefficient indices.
• Use prefix indexes to index only a subset of the data, which is typically much shorter than the full length of the column.

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

2. Determine Optimal Prefix Length:

Use the following query to determine the optimal prefix length for a given column:

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

For example, if the query returns:

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

It indicates that 80% of the strings are less than 20 characters, so a prefix length of 50 is sufficient.

3. Adjust INT Data Types:

The INT data type followed by a numeric argument (e.g., INT(1)) does not affect storage or value range. INT data types always occupy 4 bytes and allow values within the range of -2147483648 to 2147483647. The numeric argument only affects padding during display.

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