Inno vs Isam

test.rb

#!/usr/bin/env ruby
require File.dirname(__FILE__)+'/../config/environment'


ActiveRecord::Migration.create_table(:isam_items, :options => 'ENGINE=MyIsam DEFAULT CHARSET=utf8') do |t|
  t.string :item_type
end
ActiveRecord::Migration.create_table(:inno_items, :options => 'ENGINE=InnoDB DEFAULT CHARSET=utf8') do |t|
  t.string :item_type
end

class InnoItem < ActiveRecord::Base ; end
class IsamItem < ActiveRecord::Base ; end

def query(string)
  puts string
  row = ActiveRecord::Base.connection.execute(string).fetch_row
end

puts 'inserting values'
10000.times{ InnoItem.create!(:item_type => rand(2).to_s)}
10000.times{ IsamItem.create!(:item_type => rand(2).to_s)}
InnoItem.create!(:item_type => '3')
IsamItem.create!(:item_type => '3')

puts 'Testing MyIsam'
puts query("explain select * from isam_items WHERE item_type = '1';").join(", ")
puts query("explain select * from isam_items WHERE item_type = '3';").join(", ")
ActiveRecord::Migration.add_index :isam_items, :item_type
puts 'testing isam'
puts query("explain select * from isam_items WHERE item_type = '1';").join(", ")
puts query("explain select * from isam_items WHERE item_type = '3';").join(", ")


puts 'Testing InnoDB'
puts query("explain select * from inno_items WHERE item_type = '1';").join(", ")
puts query("explain select * from inno_items WHERE item_type = '3';").join(", ")
ActiveRecord::Migration.add_index :inno_items, :item_type
puts 'testing isam'
puts query("explain select * from inno_items WHERE item_type = '1';").join(", ")
puts query("explain select * from inno_items WHERE item_type = '3';").join(", ")



#clean up
ActiveRecord::Migration.drop_table :inno_items
ActiveRecord::Migration.drop_table :isam_items