Why Am I Getting the MySQL Error 'Specified Key Was Too Long'?

MySql Error: "Specified Key Was Too Long"

In querying MySql databases, the error "Specified key was too long" can arise when creating tables with long key lengths. This issue often occurs when utilizing multiple columns in a single index.

Case Study with pds_core_menu_items Table

The provided query attempts to create a table with a composite index on the columns parent_menu_id, menu_link, plugin, and alias. These columns are VARCHAR(255) data types.

Explanation of the Issue

As @Devart mentioned, the combined length of these columns exceeds the maximum allowed key length of 1000 bytes. Due to this restriction, the index creation fails with the specified error.

Solution: Prefix Indexing

The best practice to tackle this issue involves using prefix indexing. By using prefix indexes, only a left substring of the original data is indexed, significantly reducing the index size and improving efficiency.

Determining Optimal Prefix Length

To determine the optimal prefix length for each column, execute the following query:

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

Replace column_name with the column being analyzed and table_name with the table containing the column. Analyze the output to identify the smallest substring length that covers the highest percentage of rows.

Updated Query

In the provided example, indexing a substring of 50 characters would suffice, as indicated by the query results. Here's the updated query:

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

Additional Note

The error can also occur when using INT(1) and INT(32) data types. While these data types have no impact on storage size or value range, they may affect how values are displayed if the ZEROFILL option is used.

The above is the detailed content of Why Am I Getting the MySQL Error 'Specified Key Was Too Long'?. For more information, please follow other related articles on the PHP Chinese website!