SoFunction
Updated on 2025-03-09

MySQL5.6 Index Error Report 1709 Problem and Solutions

Phenomenon description

When indexing the varchar field, an error is reported as follows:

[root@localhost:(test) 13:53:27]> CREATE INDEX b_name_IDX USING BTREE ON (name);ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

View table structure:

CREATE TABLE `b` (
  `name` varchar(250) DEFAULT NULL,
  `standardized_name` varchar(250) DEFAULT NULL,
  `is_reagent` int(11) NOT NULL DEFAULT '0',
  `is_solvent` int(11) NOT NULL DEFAULT '0',
  `is_catalyst` int(11) NOT NULL DEFAULT '0',
  `is_ligand` int(11) NOT NULL DEFAULT '0',
  `to_delete` int(11) DEFAULT '0',
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Cause analysis

If the length of the index field is greater than 767, or if the length of the used field is greater than 767, an error will be reported.

MySQL 5.6innodb_large_prefixThe default is off.

In MySQL,innodb_large_prefixParameters are configuration options for InnoDB storage engine.

This parameter controls whether index prefixes exceeding 767 bytes (or 255 characters) are allowed.

By default, in MySQL 5.6 and previous versions, the InnoDB storage engine limits the maximum length of index columns to 767 bytes.

For variable-length data types such as VARCHAR, this limit includes the number of bytes that each character in the character set may take, rather than just the number of characters.

For example, if you are using a UTF-8 character set, each character may take up 1 to 4 bytes, so the actual maximum length of a VARCHAR(255) field may be much less than 255 characters.

wheninnodb_large_prefixSet asONInnoDB supports larger index prefix lengths, up to 3072 bytes.

This means you can create longer indexes, especially for columns with a large number of variable-length data types.

This is very useful for handling large data tables and situations where more complex queries are required.

To enableinnodb_large_prefix, you can add the following line in the MySQL configuration file (such as or ) and restart the MySQL service to apply the changes:

[mysqld]
innodb_large_prefix = ON

Alternatively, you can enable it at runtime by setting global variables:

SET GLOBAL innodb_large_prefix = ON;

Please note that in order to makeinnodb_large_prefixWhen effective, the following conditions must be met at the same time:

  • The database file format must be Barracuda.
  • The table format must be DYNAMIC or COMPRESSED.
  • For tables with ROW_FORMAT=COMPACT, there is still a 767-byte index prefix limit.

For more information about these conditions, see the MySQL documentation.

Problem handling

set global innodb_large_prefix=on;
show variables like 'innodb_large_prefix';
alter table b Row_format=dynamic;
set global innodb_file_format=BARRACUDA;

Add index again:

[root@localhost:(test) 13:54:18]> CREATE INDEX b_name_IDX USING BTREE ON (name);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.