mysql_love_1

faster.sql

INSERT INTO tags(name, post_tags_count, created_at, updated_at) VALUES ('latin', utc_timestamp, utc_timestamp, 1) ON DUPLICATE KEY UPDATE updated_at = utc_timestamp, post_tags_count = post_tags_count + 1, id=LAST_INSERT_ID(id)
INSERT INTO tags(name, post_tags_count, created_at, updated_at) VALUES ('illegible', utc_timestamp, utc_timestamp, 1) ON DUPLICATE KEY UPDATE updated_at = utc_timestamp, post_tags_count = post_tags_count + 1, id=LAST_INSERT_ID(id)
INSERT INTO tags(name, post_tags_count, created_at, updated_at) VALUES ('rails', utc_timestamp, utc_timestamp, 1) ON DUPLICATE KEY UPDATE updated_at = utc_timestamp, post_tags_count = post_tags_count + 1, id=LAST_INSERT_ID(id)
SELECT * FROM `tags` WHERE (`tags`.`id` IN (1,2,3)
INSERT INTO `posts` (`created_at`, `updated_at`, `text`) VALUES('2008-07-04 23:17:20', '2008-07-04 23:17:20', 'Lorem ipsum dolor sit amet...')
INSERT INTO `post_tags` (`tag_id`, `post_id`) VALUES(1, 1)
INSERT INTO `post_tags` (`tag_id`, `post_id`) VALUES(2, 1)
INSERT INTO `post_tags` (`tag_id`, `post_id`) VALUES(3, 1)

migration.rb

create_table :posts do |t|
  t.text :text
  t.timestamps
end

create_table :post_tags do |t|
  t.integer :post_id, :tag_id, :null => false
end

create_table :tags do |t|
  t.string :name
  t.string :post_tags_count
  t.timestamps
end

add_index :tags, :name, :unique => true

models_new.rb

class Post < ActiveRecord::Base
  has_many :post_tags
  has_many :tags, :through => :post_tags
  
  def tag_list=(tag_string)
    self.tag_ids = tag_string.split(',').map { |tag_name| create_or_update_tag(tag_name.squish!) }
  end
  
  def tag_list
    self.tags.map(&:name).join(', ')
  end
  
  private
    def create_or_update_tag(name)
      statement = %{
        INSERT INTO tags(name, post_tags_count, created_at, updated_at)
        VALUES (#{connection.quote(name)}, utc_timestamp, utc_timestamp, 1)
        ON DUPLICATE KEY UPDATE
          updated_at = utc_timestamp,
          post_tags_count = post_tags_count + 1,
          id=LAST_INSERT_ID(id)
      }.squish!
      
      connection.insert_sql(statement)
    end
end

class PostTag < ActiveRecord::Base
  belongs_to :tag
  belongs_to :post
end

class Tag < ActiveRecord::Base
end

models_orig.rb

class Post < ActiveRecord::Base
  has_many :post_tags
  has_many :tags, :through => :post_tags
  
  def tag_list=(tag_string)
    self.tag = tag_string.split(',').map { |tag_name| Tag.find_or_create_by_name(tag_name.squish!) }
  end
  
  def tag_list
    self.tags.map(&:name).join(', ')
  end
end

class PostTag < ActiveRecord::Base
  belongs_to :tag, :counter_cache => true
  belongs_to :post
end

class Tag < ActiveRecord::Base
end

slow.sql

SELECT * FROM tags WHERE (tags.name = 'latin') LIMIT 1
INSERT INTO tags (name, post_tags_count, created_at, updated_at) VALUES('latin', NULL, '2008-07-04 22:41:28', '2008-07-04 22:41:28')
SELECT * FROM tags WHERE (tags.name = ' illegible') LIMIT 1
INSERT INTO tags (name, post_tags_count, created_at, updated_at) VALUES(' illegible', NULL, '2008-07-04 22:41:28', '2008-07-04 22:41:28')
SELECT * FROM tags WHERE (tags.name = ' rails') LIMIT 1
INSERT INTO tags (name, post_tags_count, created_at, updated_at) VALUES(' rails', NULL, '2008-07-04 22:41:28', '2008-07-04 22:41:28')
INSERT INTO posts (created_at, updated_at, text) VALUES('2008-07-04 22:41:28', '2008-07-04 22:41:28', 'Lorem ipsum dolor sit amet...')
INSERT INTO post_tags (tag_id, post_id) VALUES(1, 1)
SELECT * FROM tags WHERE (tags.id = 1) 
UPDATE tags SET post_tags_count = post_tags_count + 1 WHERE (id = 1) 
INSERT INTO post_tags (tag_id, post_id) VALUES(2, 1)
SELECT * FROM tags WHERE (tags.id = 2) 
UPDATE tags SET post_tags_count = post_tags_count + 1 WHERE (id = 2) 
INSERT INTO post_tags (tag_id, post_id) VALUES(3, 1)
SELECT * FROM tags WHERE (tags.id = 3) 
UPDATE tags SET post_tags_count = post_tags_count + 1 WHERE (id = 3)