Why MySQL limits prefix key field length in multi column index?

[justdoit0823]

I have found an interesting aspect about MySQL index. When I create a multi column index, the prefix key field length can't be greater than a specified value.

I create a test table s_prefix_index;

create table s_prefix_index(id int, name varchar(1024), age int);

Then I create a multi column index,

mysql> create index `idx_name_age` on s_prefix_index(name, age);
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

What, I can't create that index as the error is displayed.

And, I found a question in stackoverflow.

However, I can create a single column index with the prefix key field.

mysql> create index `idx_name` on s_prefix_index(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Also, there is a document about this condition.

Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 767 bytes long for InnoDB tables or 3072 bytes if the innodb_large_prefix option is enabled. For MyISAM tables, the prefix limit is 1000 bytes. The NDB storage engine does not support prefixes (see Section 21.1.6.6, “Unsupported or Missing Features in NDB Cluster”).

This is an implementation limitation.

But, why ?

[hashar]

Given your character set (utf8mb3?) VARCHAR is 3 bytes thus VARCHAR(1024) is 3072 bytes. So creating an index on a VARCHAR(1024) just reach the limit.

The multi column index would add an INT as well which is 4 extra bytes. As I understand it that would be a key length of 1024 varchar * 3 bytes / varchar + 1 int + 4 bytes / int = 3076 > 3072.