Fix indexes for MySQL with utf8mb4 encoding

index_shortener.rb

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