May 22, 2025

MySQL Index Creation Errors: Fixing BLOB/TEXT Key Length Limits

Resolve MySQL index errors for BLOB/TEXT fields by understanding key length limitations in different MySQL versions and row formats. Optimize database design with practical solutions.

Background

A colleague encountered an error when creating an index on a MySQL table:

CREATE INDEX t_reg_code_idx USING BTREE ON t(reg_code) 
-- Error: BLOB/TEXT column 'reg_code' used in key specification without a key length

‍

This error occurs because MySQL requires specifying a key length when indexing BLOB or TEXT columns. But why?

‍

Why Does MySQL Restrict Indexes on BLOB/TEXT Columns?

MySQL enforces key length limits to ensure efficient storage and performance. For indexes on large text/blob fields:

Key Length Limits (MySQL 8.0+):
- InnoDB tables:
  - REDUNDANT/COMPACT row format: 767 bytes maximum.
  - DYNAMIC/COMPRESSED row format: 3072 bytes maximum.
- MyISAM tables: 1000 bytes maximum.
MySQL 5.7 Caveats:
- Prior to MySQL 8.0, innodb_large_prefix influenced limits (now deprecated). Ensure your row format aligns with version-specific defaults.

Validation with Examples

Test Case 1: COMPACT Row Format (Max 767 Bytes)

CREATE TABLE test01 (
  id INT AUTO_INCREMENT PRIMARY KEY,
  t_a TEXT,
  INDEX idx_t_a(t_a(10000)) -- Error: Specified key too long (max 767 bytes)
) ENGINE=InnoDB ROW_FORMAT=COMPACT;

‍

Test Case 2: COMPRESSED Row Format (Max 3072 Bytes)

CREATE TABLE test02 (
  id INT AUTO_INCREMENT PRIMARY KEY,
  t_a TEXT,
  INDEX idx_t_a(t_a(10000)) -- Error: Specified key too long (max 3072 bytes)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

‍

Design Implications and Recommendations

The error surfaced from a flawed design choice:

Root Cause: A VARCHAR field in the source system was migrated to TEXT in the target system without considering index implications.
Best Practices:
1. Avoid indexing large TEXT/BLOB fields directly. Use prefix indexes (e.g., t_a(255)) if partial matches suffice.
2. Opt for full-text search (FULLTEXT INDEX) for non-structured text.
3. Reevaluate data types: If the field stores short strings, use VARCHAR instead of TEXT.

Final Thoughts

While the error appears technical, it stems from suboptimal schema design. Prioritize thoughtful database architecture—avoid shortcuts like indiscriminate TEXT usage—to ensure scalability and maintainability. For deeper insights into MySQL indexing, explore index optimization guides.

‍