Created
January 13, 2015 19:08
-
-
Save dimaursu/0049fe403a522ad6ef8c to your computer and use it in GitHub Desktop.
Fix indexes for MySQL with utf8mb4 encoding
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
def character_columns | |
# build a hash with all the columns that contain characters | |
@character_columns ||= tables.map {|table| | |
col = columns(table) | |
.select {|column| column.type == :string || column.type == :text } | |
.map {|column| { name: column.name, type: column.sql_type} } | |
next if col.empty? | |
[table, col] | |
}.compact.to_h | |
end | |
def shorten_indexes | |
migrations ||= File.new("migrations.rb", "w"); | |
character_columns.each do |table, columns| | |
indexes(table).each do |index| | |
# compute the length of the new index < 767 bytes, which is a MySQL | |
# limitation on index column length, or 191 chars, 4byte each | |
width = 191 | |
indexed_character_columns = index.columns & columns.map {|column| column[:name] } | |
next if indexed_character_columns.empty? | |
# Assume all other indexes are 32 bit integers. | |
# Since each character in utf8mb4 counts 4 bytes, | |
# subtract one per other index key | |
width -= index.columns.size - indexed_character_columns.size | |
# character column width | |
width /= indexed_character_columns.size | |
# Still not sure whether handpicking them for each column wouldn't be | |
# better. Some columns may not actually require as much space as we give | |
# them using this method, thus other columns in the same index could | |
# benefit from the additional space | |
column_lengths = indexed_character_columns.map {|column| | |
[column, width] | |
}.to_h | |
migrations.write("remove_index #{index.table}, #{index.name}\n"); | |
migrations.write("add_index #{index.table}, #{index.columns}, name: #{index.name}, length: #{column_lengths}, using: :btree\n\n"); | |
end | |
end | |
migrations.close | |
end |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment