Skip to content

Instantly share code, notes, and snippets.

@kamipo kamipo/isucon3.sql Secret
Last active Dec 24, 2015

Embed
What would you like to do?
DROP TABLE IF EXISTS `public_memos`;
CREATE TABLE `public_memos` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`memo_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
DROP TABLE IF EXISTS `public_memos_count`;
CREATE TABLE `public_memos_count` (
`max_id` int(11) NOT NULL,
`count` int(11) NOT NULL
) ENGINE=MEMORY;
INSERT INTO public_memos(memo_id) SELECT id FROM memos WHERE is_private=0 ORDER BY id;;
DELIMITER //
CREATE TRIGGER insert_public_memos AFTER INSERT ON memos
FOR EACH ROW
BEGIN
IF NEW.is_private = 0 THEN
INSERT INTO public_memos(memo_id) VALUES (NEW.id);
END IF
END;//
CREATE TRIGGER update_public_memos_count AFTER INSERT ON public_memos
FOR EACH ROW
BEGIN
UPDATE `public_memos_count` SET max_id = NEW.id, count = count+1;
END;//
DELIMITER ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.