Specified key was too long MySQL error creating index

I stumbled across this error while coding my Ultimate RSS Reader[tm]. I was using (admittedly, not the best choice) TEXT fields to store URI for RSS feeds. Anyway, my table looked like this:

mysql> create table simple (ID integer auto_increment primary key 
not null, content text) charset utf8 engine innodb;

Query OK, 0 rows affected (0.07 sec)

mysql> show create table simple;
+--------+---------------------------------------------------------+
| Table  | Create Table                                            |
+--------+---------------------------------------------------------+
| simple | CREATE TABLE `simple` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `content` text,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------+
1 row in set (0.00 sec)

And my create index query (and error) was something along these lines:

mysql> create unique index `content_idx` on `simple` (content(256));
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

I didn’t understand why 256 (or more) characters were too much for the 767 bytes limit on InnoDB tables. Turns out that UTF8 could use up to 4 bytes for each character, and MySQL 5.1 uses by default 3 bytes for each character (this has changed in MySQL 5.5), then the limit for this value is 255 characters, or 255*3 bytes and (255*3) is less than 767.

By the way, since MySQL 5.0 the VARCHAR data type could host up to 64k characters, so the correct data type for storing your URIs could be something like VARCHAR(4096).

Many thanks to Patrizio, Stardata CEO for clarifying this issue to me!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s