Articles

Error 1709 Index Column Size Too Large The Limit Is 767 Bytes (2026)

April 18, 2026 6 min read

The Index Column Size Too Large error means a database index is trying to store more data in a single column than the storage engine allows, blocking operations that need to write to that index.

Here are the common culprits and how to fix them:

1. VARCHAR or NVARCHAR with too many characters:

  • Diagnosis: Check the CREATE TABLE or ALTER TABLE statements for columns involved in the index. Look for VARCHAR(N) or NVARCHAR(N) where N is large. For VARCHAR, the limit is effectively 767 bytes. For NVARCHAR, each character can take up to 2 bytes, so a limit of 383 characters for NVARCHAR(384) would hit the 767-byte limit.
    -- Example: Find table and column definitions
SHOW CREATE TABLE your_table_name;
  • Fix: Reduce the length of the VARCHAR or NVARCHAR column.
    -- Example: Reduce length from 1000 to 255
ALTER TABLE your_table_name MODIFY your_column_name VARCHAR(255);
    This works because the database engine has a fixed limit for the total size of columns within a single index entry. By reducing the VARCHAR length, you ensure that the data stored in that column, when combined with other indexed columns, stays within the 767-byte limit for that index.

2. Multiple VARCHAR or NVARCHAR columns in a composite index:

  • Diagnosis: Even if individual VARCHAR columns are within reasonable limits, their combined size in a composite index can exceed 767 bytes.
    -- Example: Check index definitions
SHOW INDEX FROM your_table_name;
    If an index idx_composite on (col1 VARCHAR(200), col2 VARCHAR(300), col3 VARCHAR(400)) is created, the total potential size is 900 bytes, exceeding the limit.
  • Fix:
    • Remove one or more columns from the index if they aren’t strictly necessary for query performance.
    • Reduce the length of individual VARCHAR/NVARCHAR columns in the composite index.
    -- Example: Reduce lengths for a composite index
ALTER TABLE your_table_name MODIFY col1 VARCHAR(100);
ALTER TABLE your_table_name MODIFY col2 VARCHAR(150);
ALTER TABLE your_table_name MODIFY col3 VARCHAR(200);
-- Then recreate the index if needed
DROP INDEX idx_composite ON your_table_name;
CREATE INDEX idx_composite ON your_table_name (col1, col2, col3);
    This fixes the issue by ensuring the sum of the maximum possible sizes of all columns participating in the index does not exceed the 767-byte limit.

3. BLOB or TEXT data types in an index:

  • Diagnosis: BLOB and TEXT data types (and their variants like MEDIUMBLOB, LONGTEXT) are implicitly too large to be fully indexed. If you try to index them directly, you’ll get this error.
    -- Example: Look for BLOB/TEXT columns in table definition
SHOW CREATE TABLE your_table_name;
  • Fix: You cannot directly index BLOB or TEXT columns. If you need to index parts of their content, you must create a separate, smaller column (e.g., VARCHAR or CHAR) that stores a hash or a truncated version of the BLOB/TEXT data, and index that new column.
    -- Example: Add a truncated column and index it
ALTER TABLE your_table_name ADD COLUMN truncated_description VARCHAR(255);
UPDATE your_table_name SET truncated_description = SUBSTRING(long_description, 1, 255);
CREATE INDEX idx_truncated_desc ON your_table_name (truncated_description);
    This bypasses the limitation by indexing a representation of the large data, not the data itself. The database engine is designed to prevent full indexing of these large object types to maintain index efficiency and avoid exceeding structural limits.

4. FULLTEXT index on VARCHAR or NVARCHAR exceeding limits:

  • Diagnosis: FULLTEXT indexes have their own internal limits. While they are designed for text searching, if the underlying character set and column size combination results in too much data per indexed entry, this error can manifest.
    -- Example: Check FULLTEXT index definition
SHOW CREATE TABLE your_table_name;
  • Fix: Reduce the VARCHAR or NVARCHAR length of the column(s) used in the FULLTEXT index.
    ALTER TABLE your_table_name MODIFY indexed_text_column VARCHAR(100);
-- Recreate the FULLTEXT index if necessary
DROP INDEX idx_fulltext ON your_table_name;
CREATE FULLTEXT INDEX idx_fulltext ON your_table_name (indexed_text_column);
    FULLTEXT indexes work by breaking down text into tokens. The system still needs to manage the storage and retrieval of these tokens, and the underlying storage limits for index entries apply.

5. Character set and collation contributing to byte count:

  • Diagnosis: Different character sets and collations use different numbers of bytes per character. For example, utf8mb4 can use up to 4 bytes per character, while latin1 uses only 1 byte. A VARCHAR(255) in latin1 uses 255 bytes, but in utf8mb4 it can use up to 1020 bytes. If your index is on such a column and the character set is utf8mb4, you might hit the 767-byte limit even with a seemingly moderate length.
    -- Example: Check table's default character set and collation
SHOW CREATE TABLE your_table_name;
-- Check column's character set and collation
SHOW FULL COLUMNS FROM your_table_name LIKE 'your_column_name';
  • Fix:
    • If possible, change the character set of the column to one that uses fewer bytes per character (e.g., latin1 if your data permits).
    • Alternatively, reduce the VARCHAR length more aggressively if using utf8mb4.
    -- Example: Change character set and reduce length
ALTER TABLE your_table_name CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE your_table_name MODIFY your_column_name VARCHAR(767); -- Or less, depending on your needs
    The fix works by ensuring that the maximum possible byte representation of the characters in the column, multiplied by the column length, combined with other index components, stays within the 767-byte limit.

6. Indexing ENUM or SET types with many values:

  • Diagnosis: While ENUM and SET types are stored efficiently, if an ENUM has a very large number of possible values, or a SET has many options, the internal representation might contribute to index size in unexpected ways, especially in older MySQL versions or specific configurations.
    -- Example: Check ENUM/SET definition
SHOW CREATE TABLE your_table_name;
  • Fix: Consider if indexing an ENUM or SET is truly necessary. If it is, and you suspect it’s contributing to the size limit, you might need to normalize your schema by moving these options into a separate lookup table and indexing a foreign key.
    -- Example: Create a lookup table and foreign key
CREATE TABLE your_table_name_enum_lookup (
    id INT AUTO_INCREMENT PRIMARY KEY,
    enum_value VARCHAR(50) NOT NULL UNIQUE
);
-- Populate lookup table from ENUM/SET values
ALTER TABLE your_table_name ADD COLUMN enum_lookup_id INT;
ALTER TABLE your_table_name ADD CONSTRAINT fk_enum FOREIGN KEY (enum_lookup_id) REFERENCES your_table_name_enum_lookup(id);
-- Index the new foreign key column
CREATE INDEX idx_enum_lookup ON your_table_name (enum_lookup_id);
-- Remove the original ENUM/SET column from any problematic indexes
    This approach replaces the potentially large internal representation of the ENUM/SET with a small integer foreign key, which is always well within index size limits.

The next error you’ll likely encounter after fixing index size issues is related to innodb_log_file_size if you’ve been performing many large transactions, or potentially row_too_long if you were using BLOB/TEXT and are now trying to store data that exceeds the 8126-byte row limit.

Want structured learning?

Take the full Mysql course →