Understanding Prefix Index Length Limits in MySQL 5.7 with MyISAM and utf8mb4

Recently a colleague encountered an error when adding a single‑column index to a MySQL 5.7 table with utf8mb4 charset, MyISAM engine and about 100 million rows.

MySQL imposes engine‑dependent limits on index key length: InnoDB allows up to 767 bytes (3072 bytes with innodb_large_prefix) and MyISAM allows up to 1000 bytes. Because utf8mb4 can use up to four bytes per character, the maximum indexable column length is reduced.

For utf8mb4 tables using MyISAM, the safe varchar length for a single‑column index is 250 characters (250 × 4 = 1000 bytes). For utf8 tables the limit is 333 characters (333 × 3 = 999 bytes). The article demonstrates this with test tables and shows which CREATE INDEX statements succeed or fail.

CREATE TABLE test1 (c1 varchar(250), c2 varchar(251)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

create index idx_test1_01 on test1(c1);

create index idx_test1_02 on test1(c2);  -- fails: Specified key was too long

Because creating a full‑column index on a 100‑million‑row table is slow, a prefix index can be used, e.g.,

create index idx_test_01 on test(c1(250));

Additional solutions include creating a composite index on the column and a timestamp field to narrow the scan, or using CTAS to copy the current year’s data into a new table and then applying the single‑column index.