MySQL Bugs: #16238: InnoDB Max Key Length is still 1024

Bug #16238	InnoDB Max Key Length is still 1024
Submitted:	5 Jan 2006 20:54	Modified:	7 Jan 2006 1:13
Reporter:	Michael	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	4.1.14-max-nt	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
Ref: MySQL Bug #6751

I noticed this issue had been closed. However, this clearly is a major limitation in MySQL and I would like to submit a feature request that allows some control over the maximum key length.

In byg #6751, the question is answered by saying that MySQL must be able to establish 10.000 connections, and since the entire key buffer is apparently kept in memory, this requires a lot of resources.

Or. Does it? 10.000 x 1024 is only 10 megabytes and well, I have four gigs of memory in my blade server and if you want to use another ten megs or anotehr hundred megs for that matter, well then that's alright by me. Really. You're welcome to have it.

I would also like to suggest that the value of being able to establish TEN THOUSAND simultaneous connections is perhaps not quite as valuable and useful as being able to create an index with a key length greater than a meager 1024 bytes.

In the example below I had originally wanted to create five strings of length 255 (utf) but alas, I'm limited to 65 characters before I hit the limit. This is very nearly a showstopper in my case.

This is an issue for many, as a Google groups search will reveal. I think InnoDB would benefit greatly from having more flexibility with respect to the size of keys.

How to repeat:
the following command fails due to the key length being longer than 1024 bytes.

CREATE TABLE MyViewIdx(
ViewDocDBID INT UNSIGNED,
ViewDocViewID INT UNSIGNED,
ViewDocDocID INT UNSIGNED,

ViewDocCol1String VARCHAR(80) character set utf8,
ViewDocCol1Number DOUBLE,
ViewDocCol2String VARCHAR(80) character set utf8,
ViewDocCol2Number DOUBLE,
ViewDocCol3String VARCHAR(80) character set utf8,
ViewDocCol3Number DOUBLE,
ViewDocCol4String VARCHAR(80) character set utf8,
ViewDocCol4Number DOUBLE,
ViewDocCol5String VARCHAR(80) character set utf8,
ViewDocCol5Number DOUBLE,

UNIQUE(
ViewDocDBID,
ViewDocViewID,
ViewDocCol1String,
ViewDocCol1Number,
ViewDocCol2String,
ViewDocCol2Number,
ViewDocCol3String,
ViewDocCol3Number,
ViewDocCol4String,
ViewDocCol4Number,
ViewDocCol5String,
ViewDocCol5Number
)
) TYPE=InnoDB;

Suggested fix:
Best thing would be to remove all limits on keys.

Second best thing would be to give users a server config option.

Patch solution would be to up the current limit of 1024 to 2048, in recognition of the fact that UTF-8 strings take twice as much memory as regular strings. And allow users to cut down on that 10.000 simultaneous connections number to balance the memory use if necessary.

Thank you for the bug report. Your sample request is already supported by
server 5.0:

miguel@hegel:~/dbs/4.1> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.17-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE MyViewIdx(
    ->         ViewDocDBID             INT UNSIGNED,
    ->         ViewDocViewID           INT UNSIGNED,
    ->         ViewDocDocID            INT UNSIGNED,
    -> 
    -> 
    ->         ViewDocCol1String       VARCHAR(80) character set utf8,
    ->         ViewDocCol1Number       DOUBLE,
    ->         ViewDocCol2String       VARCHAR(80) character set utf8,
    ->         ViewDocCol2Number       DOUBLE,
    ->         ViewDocCol3String       VARCHAR(80) character set utf8,
    ->         ViewDocCol3Number       DOUBLE,
    ->         ViewDocCol4String       VARCHAR(80) character set utf8,
    ->         ViewDocCol4Number       DOUBLE,
    ->         ViewDocCol5String       VARCHAR(80) character set utf8,
    ->         ViewDocCol5Number       DOUBLE,
    -> 
    -> 
    ->         UNIQUE(
    ->                 ViewDocDBID,
    ->                 ViewDocViewID,
    ->                 ViewDocCol1String,
    ->                 ViewDocCol1Number,
    ->                 ViewDocCol2String,
    ->                 ViewDocCol2Number,
    ->                 ViewDocCol3String,
    ->                 ViewDocCol3Number,
    ->                 ViewDocCol4String,
    ->                 ViewDocCol4Number,
    ->                 ViewDocCol5String,
    ->                 ViewDocCol5Number
    ->         )
    -> ) ENGINE=InnoDB;
ERROR 1071 (42000): Specified key was too long; max key length is 1024 bytes
mysql> 

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE MyViewIdx(
    ->         ViewDocDBID             INT UNSIGNED,
    ->         ViewDocViewID           INT UNSIGNED,
    ->         ViewDocDocID            INT UNSIGNED,
    -> 
    -> 
    ->         ViewDocCol1String       VARCHAR(80) character set utf8,
    ->         ViewDocCol1Number       DOUBLE,
    ->         ViewDocCol2String       VARCHAR(80) character set utf8,
    ->         ViewDocCol2Number       DOUBLE,
    ->         ViewDocCol3String       VARCHAR(80) character set utf8,
    ->         ViewDocCol3Number       DOUBLE,
    ->         ViewDocCol4String       VARCHAR(80) character set utf8,
    ->         ViewDocCol4Number       DOUBLE,
    ->         ViewDocCol5String       VARCHAR(80) character set utf8,
    ->         ViewDocCol5Number       DOUBLE,
    -> 
    -> 
    ->         UNIQUE(
    ->                 ViewDocDBID,
    ->                 ViewDocViewID,
    ->                 ViewDocCol1String,
    ->                 ViewDocCol1Number,
    ->                 ViewDocCol2String,
    ->                 ViewDocCol2Number,
    ->                 ViewDocCol3String,
    ->                 ViewDocCol3Number,
    ->                 ViewDocCol4String,
    ->                 ViewDocCol4Number,
    ->                 ViewDocCol5String,
    ->                 ViewDocCol5Number
    ->         )
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.09 sec)

mysql>

Thanks a lot for taking time to clear this up for me. I did search the bug database for all occurrences of "1024" and "limit" and found nothing to imply that this was a feature that was going into 5.0. It is not mentioned in the change notes for 5.0 either.

In section 14.2.16 of the 5.0 manual, the following can be read:

"The internal maximum key length is 3500 bytes, but MySQL itself restricts this to 1024 bytes"

I'd like to hear your comments to this, since it seems to contradict what you say. Is the manual incorrect?