チームやすべえ #isucon

isucon_tuning1.sql

-- before query
EXPLAIN
 SELECT a.id, a.title
 FROM comment c
 INNER JOIN article a ON c.article = a.id
 GROUP BY a.id ORDER BY MAX(c.created_at) DESC LIMIT 10; 


-- alter table
ALTER TABLE article
 ADD COLUMN comment_posted_at timestamp NULL DEFAULT NULL,
 ADD INDEX  comment_posted_at(comment_posted_at);

CREATE TEMPORARY TABLE article_temp (
  `id` int(11) NOT NULL,
  `comment_posted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO article_temp
 SELECT a.id, MAX(c.created_at)
 FROM comment c
 INNER JOIN article a ON c.article = a.id
 GROUP BY a.id;

UPDATE article a, article_temp at SET a.comment_posted_at = at.comment_posted_at WHERE a.id = at.id;


-- after query
EXPLAIN
 SELECT a.id, a.title
 FROM article a
 ORDER BY a.comment_posted_at DESC LIMIT 10;

isucon_tuning2.sql

DELIMITER |

CREATE TRIGGER `update_comment_posted_at` AFTER INSERT ON `comment`
  FOR EACH ROW BEGIN
    UPDATE article a SET a.comment_posted_at = NEW.created_at WHERE a.id = NEW.article;
  END;
|

DELIMITER ;