Skip to content

Instantly share code, notes, and snippets.

@ernie
Created Aug 30, 2011
Embed
What would you like to do?
MySQL query planner fail.
#!/usr/bin/env ruby
#
# $ ./wth_mysql.rb
# user system total real
# mysql 0.020000 0.010000 0.030000 ( 0.617876)
# sqlite 0.000000 0.000000 0.000000 ( 0.001234)
# postgresql 0.000000 0.000000 0.000000 ( 0.002423)
srand 123456789
require 'active_record'
require 'benchmark'
DBCONFIG = {
'sqlite' => {
:adapter => 'sqlite3',
:database => 'test_planner.sqlite3'
},
'mysql' => {
:adapter => 'mysql2',
:database => 'test_planner'
},
'postgresql' => {
:adapter => 'postgresql',
:database => 'test_planner',
:min_messages => 'warning'
}
}
class Article < ActiveRecord::Base
has_many :comments
end
class Comment < ActiveRecord::Base
belongs_to :article
end
def setup_database(db)
ActiveRecord::Base.establish_connection DBCONFIG[db]
ActiveRecord::Base.silence do
ActiveRecord::Migration.verbose = false
ActiveRecord::Schema.define do
create_table :articles, :force => true do |t|
t.string :title
t.text :body
t.boolean :published, :null => false, :default => false
t.timestamps
end
create_table :comments, :force => true do |t|
t.belongs_to :article
t.string :email
t.text :body
t.timestamps
end
add_index :comments, :article_id
end
articles = (1..100).map do |id|
Article.create!(:title => "Article Number #{id}", :body => "blah" * 1000, :published => !(id % 20).zero?)
end
20000.times do |id|
article = articles.sample
article.comments.create! :email => "number#{id}@example.com", :body => "Comment #{id} on Article #{article.id}" * 20
end
end
end
Benchmark.bm(10) do |x|
%w( mysql sqlite postgresql ).each do |db|
setup_database db
x.report db do
Comment.joins(:article).
where(:articles => {:published => true}).
order('comments.id desc').limit(3).all
end
end
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment