🚨 MySQL Error: 'ERROR 1170 (42000): BLOB/TEXT column used in key specification without a key length' — Cause, Fix, and Troubleshooting Guide

Overview

ERROR 1170 (42000) occurs when you index a TEXT or BLOB column (or their LONG/MEDIUM variants) without specifying how many leading bytes/characters to index:

ERROR 1170 (42000): BLOB/TEXT column 'content' used in key specification without a key length

MySQL cannot build a full index on an unbounded column, so it requires a prefix length — the number of leading characters to include in the index. The fix is to add that length, or to reconsider whether the column should be VARCHAR instead.

Symptoms

• A CREATE TABLE/ALTER TABLE ADD INDEX fails on a TEXT/BLOB column.
• Adding a UNIQUE or PRIMARY KEY on a text column fails.
• A dump from a system that allowed it (or a different engine) fails to import.

Common Root Causes

1. Indexing a TEXT/BLOB with no prefix

CREATE TABLE docs (
  id INT PRIMARY KEY,
  content TEXT,
  KEY (content)
);

ERROR 1170 (42000): BLOB/TEXT column 'content' used in key specification without a key length

2. A UNIQUE constraint on a text column

Uniqueness on a prefix is rarely what you want, but it still requires a length.

3. The column should really be VARCHAR

A short “slug” or “code” modeled as TEXT triggers 1170; VARCHAR(n) can be indexed in full.

How to diagnose

Confirm the column’s type — only the LOB family needs a prefix:

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='docs';

+-------------+-----------+--------------------------+
| COLUMN_NAME | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH  |
+-------------+-----------+--------------------------+
| content     | text      | 65535                    |
+-------------+-----------+--------------------------+

Fixes

Add a prefix length that is long enough to be selective but within index limits:

ALTER TABLE docs ADD INDEX idx_content (content(191));

Under utf8mb4, keep prefixes at or below 191 characters when the index must fit InnoDB’s 3072-byte key limit (191 x 4 bytes ≈ 764 bytes per column, safe within a composite key too).

If the column is really short, convert it to VARCHAR and index it fully:

ALTER TABLE docs MODIFY content VARCHAR(255);
ALTER TABLE docs ADD INDEX idx_content (content);

For exact-match lookups on long text, index a hash generated column instead of a prefix:

ALTER TABLE docs
  ADD COLUMN content_hash BINARY(32)
    AS (UNHEX(SHA2(content, 256))) STORED,
  ADD INDEX idx_content_hash (content_hash);

What to watch out for

• Prefix indexes only accelerate LIKE 'x%' and equality on the prefix; they cannot be used for ORDER BY on the full column or for covering reads.
• A too-short prefix (e.g. 10 chars) is barely selective and wastes the index; size it to the data’s real distinctness.
• utf8mb4 multiplies bytes by 4 — a prefix of 255 chars can exceed the 3072-byte key limit and raise ERROR 1071 instead. Keep it at 191 unless you have measured.
• Hash-column indexing gives true equality lookups without a prefix, at the cost of an extra column.

Related

• ERROR 1071: Specified key was too long — the key-length ceiling you can hit when the prefix is too large.
• ERROR 1064: SQL syntax error — the general DDL parse error while editing indexes.
• ERROR 1406: Data too long for column — related length handling when values overflow a VARCHAR.