P0uW7nvG

1## dump [sql]
2
3CREATE DATABASE /*!32312 IF NOT EXISTS*/`index_test` /*!40100 DEFAULT CHARACTER SET utf8 */;
4
5/*Table structure for table `poly_table` */
6
7DROP TABLE IF EXISTS `poly_table`;
8
9CREATE TABLE `poly_table` (
10  `id` int(11) NOT NULL AUTO_INCREMENT,
11  `poly_id` int(11) DEFAULT NULL,
12  `poly_type` varchar(255) DEFAULT NULL,
13  `data` text,
14  PRIMARY KEY (`id`),
15  KEY `id_type` (`poly_id`,`poly_type`),
16  KEY `type_id` (`poly_type`,`poly_id`)
17) ENGINE=InnoDB AUTO_INCREMENT=61818 DEFAULT CHARSET=utf8;
18
19## benchmark.rb [ruby]
20
21require 'rubygems'
22require 'mysql'
23require 'active_record'
24require 'logger'
25
26class TestModel < ActiveRecord::Base
27  set_table_name 'poly_table'
28end
29
30#ActiveRecord::Base.logger = Logger.new(STDERR)
31
32ActiveRecord::Base.establish_connection(:adapter  => 'mysql2',
33  :username => 'root',
34  :host     => 'localhost',
35  :database => 'index_test'
36)
37
38n = 100_000
39
40# insert trash
41trash = %w(few sample models go here and few other too)
42key_pairs = []
43
44n.times do |i|
45  rand_id   = rand(n)
46  rand_type = trash[rand(trash.size)]
47  key_pairs << [rand_id, rand_type]
48end
49
50puts "inserting random data"
51TestModel.delete_all
52n.times do
53  random_pair = key_pairs[rand(key_pairs.size)]
54  TestModel.create :poly_id => random_pair[0], :poly_type => random_pair[1], :data => 'alskhf lasdhfalkhf alskhf alksdjhf alsjkhfa lsjkhfa lsdjkhf'
55end
56
57Benchmark.bm do |b|
58  b.report "ID, TYPE" do
59    n.times do
60      random_pair = key_pairs[rand(key_pairs.size)]
61      TestModel.all :select => 'SQL_NO_CACHE *', :from => 'poly_table USE INDEX (`id_type`)', :conditions => { :poly_id => random_pair[0], :poly_type => random_pair[1] }
62    end
63  end
64
65  b.report "TYPE, ID" do
66    n.times do
67      random_pair = key_pairs[rand(key_pairs.size)]
68      TestModel.all :select => 'SQL_NO_CACHE *', :from => 'poly_table USE INDEX (`type_id`)', :conditions => { :poly_id => random_pair[0], :poly_type => random_pair[1] }
69    end
70  end
71end
72
73## output [plain_text]
74
75      user     system      total        real
76ID, TYPE 33.500000   3.300000  36.800000 ( 48.320815)
77TYPE, ID 33.380000   3.260000  36.640000 ( 48.308469)