Menu
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?
530
That is, the maximum index key length is 1536 bytes when the page size is 8KB, and 768 bytes when the page size is 4KB. The limits that apply to index key prefixes also apply to full-column index keys. A maximum of 16 columns is permitted for multicolumn indexes.
ALTER command to add and drop INDEXALTER TABLE tbl_name ADD INDEX index_name (column_list) − This adds an ordinary index in which any value may appear more than once. ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list) − This creates a special FULLTEXT index that is used for text-searching purposes.
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))
From the MySQL manual:
"The maximum key length is 1000 bytes. This can also be changed by changing the source and recompiling"
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
