📋

Description

Error 1071 occurs when attempting to create an index (e.g., PRIMARY KEY, UNIQUE, INDEX) where the combined length of the indexed columns exceeds the maximum allowed key length. This typically happens during `CREATE TABLE`, `ALTER TABLE`, or `CREATE INDEX` statements, preventing schema modifications.

💬

Error Message

Specified key was too long; max key length is %d bytes

🔍

Known Causes

4 known causes

⚠️

Long Column Data Types

Indexing entire columns with very long `VARCHAR` or `TEXT` data types can exceed the maximum key length allowed by the database.

⚠️

Multi-byte Character Sets

Character sets like `utf8mb4` use more bytes per character, which significantly reduces the effective character limit for an index key.

⚠️

Storage Engine Limits

Different storage engines (e.g., InnoDB, MyISAM) have distinct maximum key length restrictions that can be exceeded by your index definition.

⚠️

Composite Index Length

When creating a composite index on multiple columns, the sum of their individual lengths can easily surpass the maximum allowed key length.

🛠️

Solutions

4 solutions available

1. Reduce Column Length for Index easy

Shorten the data type of columns included in the index.

1

Identify the table and columns involved in the index causing the error. You can often infer this from the `CREATE TABLE` or `ALTER TABLE` statement that failed.

SHOW CREATE TABLE your_table_name;

2

Examine the `CREATE TABLE` output and locate the index definition. Determine which columns are part of the index and their current data types.

-- Example output from SHOW CREATE TABLE:
CREATE TABLE `your_table_name` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `long_text_col` varchar(2000) DEFAULT NULL,
  `another_col` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_long_text` (`long_text_col`(255)) -- This might be the issue
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3

Modify the table definition to use a shorter data type or a prefix for the column in the index. For `VARCHAR` or `TEXT` types, using a prefix is common.

-- Option 1: Reduce the length of the VARCHAR column itself
ALTER TABLE your_table_name MODIFY COLUMN long_text_col VARCHAR(500);

-- Option 2: Adjust the index prefix (if the column is too long for the index)
-- Note: If you modify the column type, you might need to drop and re-create the index.
-- If the column type is already reasonable but the index is still too long, consider prefixing.
ALTER TABLE your_table_name DROP INDEX idx_long_text;
ALTER TABLE your_table_name ADD INDEX idx_long_text (long_text_col(255)); -- Adjust 255 as needed

4

Re-run the `CREATE TABLE` or `ALTER TABLE` statement that initially failed.

--- (Re-run your original DDL statement here) ---

2. Adjust InnoDB Index Prefix Length medium

Configure InnoDB to allow longer index prefixes for text columns.

1

Understand that MariaDB's InnoDB storage engine has a default maximum index key length (often 767 bytes for older versions or for certain character sets). This error occurs when the sum of the lengths of columns in an index exceeds this limit.

SELECT @@innodb_index_prefix_length;

2

Modify the `innodb_index_prefix_length` system variable. This change usually requires a server restart. The maximum value is typically 3072 bytes (which corresponds to 1024 characters for `utf8mb4` if each character is 3 bytes).

-- To change dynamically (temporary, until restart):
SET GLOBAL innodb_index_prefix_length = 3072;

-- To make it permanent, edit your MariaDB configuration file (e.g., my.cnf or mariadb.conf.d/*.cnf):
[mysqld]
innodb_index_prefix_length = 3072

3

Restart the MariaDB server for the configuration change to take effect.

# On systemd-based systems:
systemctl restart mariadb

# On older init systems:
service mariadb restart

4

Re-run the `CREATE TABLE` or `ALTER TABLE` statement that failed.

--- (Re-run your original DDL statement here) ---

3. Use FULLTEXT Index for Text Search medium

Leverage FULLTEXT indexes for searching large text columns instead of regular B-tree indexes.

1

Determine if the index is intended for full-text searching. If you're indexing large `TEXT` or `VARCHAR` columns to search for keywords within them, a regular B-tree index is inefficient and prone to the 'key too long' error.

SHOW CREATE TABLE your_table_name;

2

Drop the problematic index if it's for full-text search purposes.

ALTER TABLE your_table_name DROP INDEX idx_problematic_text;

3

Add a `FULLTEXT` index to the relevant column(s). `FULLTEXT` indexes are specifically designed for searching within text content and do not have the same key length limitations as B-tree indexes.

ALTER TABLE your_table_name ADD FULLTEXT INDEX ft_idx_text (your_long_text_column);

4

Update your application's queries to use `MATCH() AGAINST()` syntax for searching the text column.

SELECT * FROM your_table_name WHERE MATCH(your_long_text_column) AGAINST('search term' IN BOOLEAN MODE);

4. Re-evaluate Indexing Strategy advanced

Critically review which columns truly need to be indexed and consider composite index optimization.

1

Analyze your application's query patterns. Identify which queries are slow or failing due to the 'key too long' error. This often involves looking at `EXPLAIN` output.

EXPLAIN SELECT * FROM your_table_name WHERE some_column = 'value';
-- Analyze the output for missing or inefficient indexes.

2

Consider if all columns in the problematic index are essential for performance. Sometimes, an index is created on too many columns or columns that are rarely used in `WHERE` clauses.

SHOW CREATE TABLE your_table_name;

3

If the index is a composite index (multiple columns), evaluate if a shorter prefix on one or more columns can suffice, or if the composite index can be broken down into smaller, more targeted indexes.

-- Example: If you have an index on (col1(50), col2(1000)), and col2 is the issue:
-- Option A: Reduce col2 prefix in index
ALTER TABLE your_table_name DROP INDEX composite_idx;
ALTER TABLE your_table_name ADD INDEX composite_idx (col1(50), col2(255));

-- Option B: Separate indexes if col2 is also queried independently
ALTER TABLE your_table_name ADD INDEX idx_col1 (col1(50));
ALTER TABLE your_table_name ADD INDEX idx_col2 (col2(255)); -- Note: This might not be ideal if col2 is always queried with col1

4

If columns are of `TEXT` or `BLOB` types, they are generally not suitable for direct indexing due to their size. Consider indexing a prefix or using `FULLTEXT` indexes (as in Solution 3).

--- (No direct SQL for this step, it's a design decision) ---

🔗

Related Errors

5 related errors

MariaDB Error 1071: Index Key Too Long