How to increase key length in MySQL 5.1?

Question

When doing a Rails migration that creates an index, I get:

Mysql::Error: Specified key was too long; max key length is 1000 bytes: CREATE UNIQUE INDEX `index_matches_on_foo_and_bar_id_and_baz_id` ON `matches` (`foo`, `bar_id`, `baz_id`) 

What MySQL variable do I need to set to increase it, and where to set it so that it affects all sessions, and not just the current client session?

Answer 1

InnoDB actually only allows 768 BYTES in the index. Also note that UTF-8 encoded strings occupy 3 bytes per character, so in this case you should only have 768/3 characters.

A possible solution is to limit the length of the use of the field in the index. However, since you also need a unique index, this may not be an acceptable solution for you. Use the following to limit the length of fields used.

 CREATE UNIQUE INDEX `index_matches_on_foo_and_bar_id_and_baz_id` ON `matches` (`foo`(100), `bar_id`(100), `baz_id`(100)) 

Answer 2

From the MySQL manual:

"The maximum key length is 1000 bytes. You can also change this by changing the source and recompiling."