Why Does MySQL Return 'Specified Key Was Too Long; Max Key Length is 1000 Bytes' and How Can I Fix It?

"Specified Key Was Too Long; Max Key Length is 1000 Bytes" Error Explained

Problem Outline:

When attempting to execute the following query:

CREATE TABLE IF NOT EXISTS `pds_core_menu_items` (
  ...
  KEY `index` (`parent_menu_id`,`menu_link`,`plugin`,`alias`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

an error is encountered: "#1071 - Specified key was too long; max key length is 1000 bytes".

Explanation:

This error arises when the combined length of the columns in the index exceeds the maximum allowed key length of 1000 bytes. In this case, the index created on the pds_core_menu_items table includes four columns: parent_menu_id, menu_link, plugin, and alias, which have lengths of 32, 255, 255, and 255 bytes, respectively.

Solution:

To resolve this issue, reduce the size of the index by using shorter column lengths or by limiting the number of columns included in the index.

Recommended Approach:

The recommended approach is to use prefix indexes to optimize index size and performance. Prefix indexes involve indexing only the first few characters of a string column. This approach can significantly reduce the index length.

For instance, the following modified query creates a prefix index on each of the four columns:

CREATE TABLE IF NOT EXISTS `pds_core_menu_items` (
  ...
  KEY `index` (`parent_menu_id`,`menu_link`(50),`plugin`(50),`alias`(50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Additional Note:

Additionally, it's worth noting that the INT(1) and INT(32) data types in the query don't affect the storage size or value range of the columns. INT always uses 4 bytes of storage and allows values from -2147483648 to 2147483647. The numeric argument can influence the padding of values during display, which is not relevant unless ZEROFILL is used.

The above is the detailed content of Why Does MySQL Return 'Specified Key Was Too Long; Max Key Length is 1000 Bytes' and How Can I Fix It?. For more information, please follow other related articles on the PHP Chinese website!