When running 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 this, and where do I set it so it affects all sessions, and not just the current client session?
解决方案
InnoDB actually only allows 768 BYTES in the index. Also, note that UTF-8 encoded strings take up 3 bytes per character, so you only have 768 / 3 chars to play with in the index in that case.
A possible solution is to limit the length of the field use in the index. However, since you also want a unique index, that might not be an acceptable solution for you. Use the following to limit the length of the 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))