test.rb

require 'rubygems'
require 'mysql'
require 'benchmark'

db = Mysql.new("localhost", "root", "", "test")


# INNODB - ENUM vs TEXT vs INT

Benchmark.bm do|b|
  b.report("INNO DB: VARCHAR") do
    10.times do
        db.query("SELECT * FROM innodb WHERE varcharen = 'patrick'")
    end
  end
  b.report("INNO DB: ENUM") do  
    10.times do
        db.query("SELECT * FROM innodb WHERE enumen = 'patrick'")
    end
  end
  b.report("INNO DB: INTEN") do  
    10.times do
        db.query("SELECT * FROM innodb WHERE inten = 50")
    end
  end
end

puts"------------------------------------------------"
# INNODB INDEXED - ENUM vs TEXT vs INT

Benchmark.bm do|b|
  b.report("INNO DB INDEXED: VARCHAR") do
    10.times do
        db.query("SELECT * FROM innodb_index WHERE varcharen = 'patrick'")
    end
  end
  b.report("INNO DB INDEXED: ENUM") do  
    10.times do
        db.query("SELECT * FROM innodb_index WHERE enumen = 'patrick'")
    end
  end
  b.report("INNO DB INDEXED: INTEN") do  
    10.times do
        db.query("SELECT * FROM innodb_index WHERE inten = 50")
    end
  end
end

puts"------------------------------------------------"
# MYISAM - ENUM vs TEXT vs INT

Benchmark.bm do|b|
  b.report("MYISAM: VARCHAR") do
    10.times do
        db.query("SELECT * FROM myisam WHERE varcharen = 'patrick'")
    end
  end
  b.report("MYISAM: ENUM") do  
    10.times do
        db.query("SELECT * FROM myisam WHERE enumen = 'patrick'")
    end
  end
  b.report("MYISAM: INTEN") do  
    10.times do
        db.query("SELECT * FROM myisam WHERE inten = 50")
    end
  end
end

puts"------------------------------------------------"
# MYISAM INDEXED - ENUM vs TEXT vs INT

Benchmark.bm do|b|
  b.report("MYISAM INDEXED: VARCHAR") do
    10.times do
        db.query("SELECT * FROM myisam_index WHERE varcharen = 'patrick'")
    end
  end
  b.report("MYISAM INDEXED: ENUM") do  
    10.times do
        db.query("SELECT * FROM myisam_index WHERE enumen = 'patrick'")
    end
  end
  b.report("MYISAM INDEXED: INTEN") do  
    10.times do
        db.query("SELECT * FROM myisam_index WHERE inten = 50")
    end
  end
end