MySQL error: Specified key was too long; max key length is 1000 bytes

Question

There are a few other questions about this error, but I'm having a hard time getting my head around this issue.

I'm trying to make a very basic MySQL table. I'm attempting to keep everying thing in utf8, which I do understand takes more bytes than a normal char set.

CREATE TABLE `bibliography` (
  `id` int(5) unsigned zerofill NOT NULL,
  `pub_type` varchar(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `pub_genre` varchar(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `author_first` varchar(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `author_last` varchar(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `publication` varchar(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `pub_date` date NOT NULL,
  `pub_city` varchar(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `pub_country` varchar(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `html` text NOT NULL,
  `live` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `pub_type` (`pub_type`,`pub_genre`,`title`,`author_first`,`author_last`,`publication`,`pub_date`,`pub_city`,`pub_country`),
  KEY `live` (`live`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Okay, so above works, but obviously I can't live 5 character author names. When I try to raise the limit:

ALTER TABLE `bibliography` CHANGE `author_first` `author_first` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL 

I get the error:

1071 - Specified key was too long; max key length is 1000 bytes

So, I don't get it. Does the 1000 byte limit apply across the whole table? Obviously it does, right?

In light of the answers, can someone enlighten me on the difference between setting up an index like:

KEY `live` (`live`),
  KEY `pub_type` (`pub_type`),
  KEY `pub_genre` (`pub_genre`),
  KEY `author_last` (`author_last`),
  KEY `publication` (`publication`) 

and like:

KEY `pub_type` (`pub_type`,`pub_genre`,`title`,`author_first`,`author_last`,`publication`,`pub_date`,`pub_city`)

I'm starting to understand here, that the KEY is limited to 1000 bytes. But you can have multiple keys in a table. How is combining a bunch of fields into one key different form assigning a key to each different field?

Answer 1

No, it doesn't apply across the whole table. But, you're using a unicode field that needs up to 3 bytes per character, so a key that includes a VARCHAR(50) unicode field translates to 150 bytes in MySQL. So, your total is:

KEY `pub_type` (
    `pub_type`, 5
    `pub_genre`, 5
    `title`, 255
    `author_first`, 5
    `author_last`, 5
    `publication`, 5
    `pub_date`, 3 bytes
    `pub_city`, 5
    `pub_country`5 
)

= 7*5 + 255 = 290 * 3 = 870 + 3 = 873 bytes. You only have 1000 - 873 = 127 bytes to expand, which translates to about 127 / 3 = 42 more characters you can expand your fields by. Not a safe bet.

You're using indices (keys) wrong. There's no point in having an index on every field. If you're trying to speed up your query, only include the fields that are most often used in your WHERE clause.

Answer 2

Change your pub_type key to only include pub_type, pub_genre, and title. Odds are that this key will be almost as good as your current key for speeding up queries, and will let you have long author names, etc without violating the maximum key length restriction on tables.