Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL refuses to index a TEXT or BLOB column

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

Short answer

MySQL cannot index a whole TEXT or BLOB value, so it demands a prefix length for the key. Write the column as name(255) in the index, or change the column to VARCHAR when the values are short. A prefix length counts characters for text types, and the key has a byte limit.

  • Applies to MySQL 8.4, InnoDB
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Find which column is TEXT or BLOB and which key mentions it. The error names the column.

    SHOW CREATE TABLE posts\G
  2. 2Match what you see, then jump to its fix

    1. 1The error names a TEXT, MEDIUMTEXT, LONGTEXT or BLOB column used in PRIMARY KEY, UNIQUE or INDEXA TEXT or BLOB column is in a key without a prefixFix
    2. 2The TEXT column is a name, slug or email that is used for lookups and uniquenessThe column holds short values and should be VARCHARFix
    3. 3A prefix on the column is not enough, for example a lookup on the first part of a long stringYou want an index on an expression of a long columnFix

What does the error look like?

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

Cause 1 of 3

A TEXT or BLOB column is in a key without a prefix

1Check

Prefixes are mandatory for BLOB and TEXT key parts. Read the column types in the table definition.

SHOW COLUMNS FROM posts;

2Fix

Give the key part a length. For nonbinary types such as TEXT the length is counted in characters, for BLOB types in bytes.

CREATE INDEX idx_title ON posts (title(191));

3Verify

The index is listed with a Sub_part value.

SHOW INDEX FROM posts;

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

The column holds short values and should be VARCHAR

1Check

Find the longest value actually stored. A prefix index on a UNIQUE key only enforces uniqueness for the prefix, so a column that is searched whole is better as VARCHAR.

SELECT MAX(CHAR_LENGTH(title)) FROM posts;

2Fix

Change the type to VARCHAR with enough room, then add the index. For InnoDB the key limit is 3072 bytes for DYNAMIC and COMPRESSED row formats and 767 bytes for REDUNDANT and COMPACT, counted in bytes, so a multibyte character set uses more of it per character.

ALTER TABLE posts MODIFY title VARCHAR(255) NOT NULL;
CREATE UNIQUE INDEX uq_title ON posts (title);

3Verify

The statement runs without error 1170.

SHOW CREATE TABLE posts\G

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

You want an index on an expression of a long column

1Check

A functional key part cannot be a bare column name and does not accept a prefix length. The manual's way to shorten a column there is SUBSTRING() or CAST().

SELECT VERSION();

2Fix

Index the expression, wrapped in a second pair of parentheses.

CREATE INDEX idx_title_part ON posts ((SUBSTRING(title, 1, 100)));

3Verify

Queries that use the same expression can use the index; confirm it appears in the index list.

SHOW INDEX FROM posts;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Choose VARCHAR for anything that you search or enforce as unique, and keep TEXT for body content.
  • Check the character set when you pick a prefix length, since the limit is counted in bytes.
  • Run schema changes on a copy first so an index error does not stop a deploy halfway.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.