Open kappybar opened 9 months ago
Count: 125296 Time=0.00s (136s) Lock=0.00s (0s) Rows=1.0 (125296), isucon[isucon]@localhost SELECT IFNULL(SUM(l2.tip), N) FROM livestreams l INNER JOIN livecomments l2 ON l.id = l2.livestream_id WHERE l.id = N Count: 4041 Time=0.03s (130s) Lock=0.00s (0s) Rows=4.1 (16512), isucon[isucon]@localhost SELECT * FROM livestream_tags WHERE livestream_id = N Count: 125298 Time=0.00s (114s) Lock=0.00s (0s) Rows=1.0 (125298), isucon[isucon]@localhost SELECT COUNT(*) FROM livestreams l INNER JOIN reactions r ON l.id = r.livestream_id WHERE l.id = N Count: 676925 Time=0.00s (92s) Lock=0.00s (0s) Rows=0.5 (319061), 2users@localhost # Count: 8220 Time=0.01s (51s) Lock=0.00s (0s) Rows=0.8 (6885), isucon[isucon]@localhost SELECT image FROM icons WHERE user_id = N Count: 3230 Time=0.01s (31s) Lock=0.00s (0s) Rows=1.0 (3230), isucon[isucon]@localhost SELECT IFNULL(SUM(l2.tip), N) FROM users u INNER JOIN livestreams l ON l.user_id = u.id INNER JOIN livecomments l2 ON l2.livestream_id = l.id WHERE u.id = N Count: 3232 Time=0.01s (30s) Lock=0.00s (0s) Rows=1.0 (3232), isucon[isucon]@localhost SELECT COUNT(*) FROM users u INNER JOIN livestreams l ON l.user_id = u.id INNER JOIN reactions r ON r.livestream_id = l.id WHERE u.id = N Count: 29058 Time=0.00s (16s) Lock=0.00s (0s) Rows=0.0 (0), isucon[isucon]@localhost DELETE FROM livecomments WHERE id = N AND livestream_id = N AND (SELECT COUNT(*) FROM (SELECT 'S' AS text) AS texts INNER JOIN (SELECT CONCAT('S', 'S', 'S') AS pattern) AS patterns ON texts.text LIKE patterns.pattern) >= N Count: 611 Time=0.03s (15s) Lock=0.00s (0s) Rows=1.0 (611), isucon[isucon]@localhost SELECT slot FROM reservation_slots WHERE start_at = N AND end_at = N Count: 6608 Time=0.00s (15s) Lock=0.00s (0s) Rows=1.0 (6608), isucon[isucon]@localhost SELECT * FROM themes WHERE user_id = N Count: 185 Time=0.06s (10s) Lock=0.00s (0s) Rows=0.3 (62), isucon[isucon]@localhost SELECT id, user_id, livestream_id, word FROM ng_words WHERE user_id = N AND livestream_id = N Count: 1540 Time=0.00s (7s) Lock=0.00s (0s) Rows=0.0 (0), isucon[isucon]@localhost COMMIT Count: 16501 Time=0.00s (6s) Lock=0.00s (0s) Rows=1.0 (16501), isucon[isucon]@localhost SELECT * FROM tags WHERE id = N Count: 55 Time=0.08s (4s) Lock=0.00s (0s) Rows=0.0 (0), isucon[isucon]@localhost UPDATE reservation_slots SET slot = slot - N WHERE start_at >= N AND end_at <= N Count: 1000 Time=0.00s (4s) Lock=0.00s (0s) Rows=0.0 (0), isucon[isucon]@localhost INSERT INTO users (id, name, display_name, description, password) VALUES (N, 'S', 'S', 'S', 'S') Count: 55 Time=0.07s (3s) Lock=0.00s (0s) Rows=11.1 (611), isucon[isucon]@localhost SELECT * FROM reservation_slots WHERE start_at >= N AND end_at <= N FOR UPDATE Count: 191 Time=0.02s (3s) Lock=0.00s (0s) Rows=0.0 (0), isudns[isudns]@localhost delete from records where domain_id=N and name='S' and type='S' Count: 6434 Time=0.00s (2s) Lock=0.00s (0s) Rows=1.0 (6434), isucon[isucon]@localhost SELECT * FROM users WHERE id = N Count: 50 Time=0.05s (2s) Lock=0.00s (0s) Rows=1.0 (48), isucon[isucon]@localhost SELECT * FROM livestreams WHERE user_id = N Count: 177 Time=0.01s (2s) Lock=0.00s (0s) Rows=0.0 (0), isucon[isucon]@localhost INSERT INTO icons (user_id, image) VALUES (N, 'S') Count: 510 Time=0.00s (2s) Lock=0.00s (0s) Rows=0.0 (0), isucon[isucon]@localhost INSERT INTO themes (user_id, dark_mode) VALUES (N, true) Count: 490 Time=0.00s (1s) Lock=0.00s (0s) Rows=0.0 (0), isucon[isucon]@localhost INSERT INTO themes (user_id, dark_mode) VALUES (N, false) Count: 382 Time=0.00s (1s) Lock=0.00s (0s) Rows=0.5 (191), isudns[isudns]@localhost SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=N and type='S' and name='S' and domain_id=N Count: 192 Time=0.01s (1s) Lock=0.00s (0s) Rows=0.0 (0), isudns[isudns]@localhost commit Count: 3138 Time=0.00s (1s) Lock=0.00s (0s) Rows=1.0 (3138), isucon[isucon]@localhost SELECT * FROM livestreams WHERE id = N Count: 19 Time=0.06s (1s) Lock=0.00s (0s) Rows=0.2 (3), isucon[isucon]@localhost SELECT * FROM ng_words WHERE user_id = N AND livestream_id = N ORDER BY created_at DESC Count: 19 Time=0.06s (1s) Lock=0.00s (0s) Rows=1.7 (32), isucon[isucon]@localhost SELECT * FROM ng_words WHERE livestream_id = N Count: 191 Time=0.01s (0s) Lock=0.00s (0s) Rows=0.0 (0), isudns[isudns]@localhost SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=N and name='S' and domain_id=N Count: 177 Time=0.01s (0s) Lock=0.00s (0s) Rows=0.0 (0), isucon[isucon]@localhost
IFNULL(SUM(l2.tip), N) SUM(l2.tip) が null なら N, そうでないならSUM(l2.tip)をかえす。
IFNULL(SUM(l2.tip), N)
sum()は一件でもnull が入っているなら答えが null になる。