Last Updated: February 25, 2016
·
873
· tiegz

ActiveRecord::Base.average_coumn_lengths

TODO: need to fix this for diff column types; for instance, INT should always be 4 bytes

require 'active_support/concern'
module AverageColumnLength
  extend ActiveSupport::Concern

  # Returns a map of column names to their average row
  # length (in bytes), based on a +sample_size+ of rows. 
  # Warning +random=true+ slows it down.
  #
  # The +avg_row_length+ returned from MySQL's +SHOW 
  # TABLE STATUS+ is often innacurate. You can calculate 
  # this average row length by calling 
  # +average_column_lengths.values.sum+
  module ClassMethods
    def average_column_lengths(size=100, rand=false)
      i, o = [], []
      columns.each { |c| 
        i << "LENGTH(#{c.name}) AS sum_#{c.name}" 
        o << "AVG(results.sum_#{c.name}) AS avg_#{c.name}"
      }
      iq  = "SELECT #{i.join(', ')}"
      iq << " FROM #{table_name}"
      iq << " ORDER BY RAND()" if rand
      iq << " LIMIT #{size.to_i}"
      oq  = "SELECT #{o.join(', ')}"
      oq << " FROM (#{iq}) results"

      r = find_by_sql(oq).first

      columns.each_with_object({}) { |c, _|
        _[c.name] = r.send("avg_#{c.name}").to_i
      }
    end
  end
end
ActiveRecord::Base.send(:include, AverageColumnLength)