sql1436 - Githubissues

++ dirname ./slowquery.sh
+ cd .
+ rotate_filepath=
+ '[' 0 -eq 1 ']'
+ sudo mysqldumpslow -s t /var/log/mysql/mysql-slow.log
+ head -n 100

Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 15166  Time=0.01s (101s)  Lock=0.00s (0s)  Rows=0.8 (12196), isucon[isucon]@localhost
  SELECT image FROM icons WHERE user_id = N

Count: 252301  Time=0.00s (55s)  Lock=0.00s (0s)  Rows=0.5 (115214), 2users@localhost
  #

Count: 12602  Time=0.00s (27s)  Lock=0.00s (0s)  Rows=1.0 (12602), isucon[isucon]@localhost
  SELECT * FROM themes WHERE user_id = N

Count: 802  Time=0.02s (19s)  Lock=0.00s (0s)  Rows=1.0 (802), isucon[isucon]@localhost
  SELECT slot FROM reservation_slots WHERE start_at = N AND end_at = N

Count: 326  Time=0.06s (18s)  Lock=0.00s (0s)  Rows=0.2 (79), isucon[isucon]@localhost
  SELECT id, user_id, livestream_id, word FROM ng_words WHERE user_id = N AND livestream_id = N

Count: 30689  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: 30800  Time=0.00s (11s)  Lock=0.00s (0s)  Rows=1.0 (30800), isucon[isucon]@localhost
  SELECT * FROM tags WHERE id = N

Count: 2480  Time=0.00s (9s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  COMMIT

Count: 111  Time=0.06s (6s)  Lock=0.00s (0s)  Rows=1.3 (145), isucon[isucon]@localhost
  SELECT * FROM livestreams WHERE user_id = N

Count: 95  Time=0.06s (5s)  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: 12373  Time=0.00s (4s)  Lock=0.00s (0s)  Rows=1.0 (12373), isucon[isucon]@localhost
  SELECT * FROM users WHERE id = N

Count: 97  Time=0.05s (4s)  Lock=0.00s (0s)  Rows=8.4 (811), isucon[isucon]@localhost
  SELECT * FROM reservation_slots WHERE start_at >= N AND end_at <= N FOR UPDATE

Count: 7946  Time=0.00s (4s)  Lock=0.00s (0s)  Rows=3.9 (30802), isucon[isucon]@localhost
  SELECT * FROM livestream_tags WHERE livestream_id = 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: 224  Time=0.02s (4s)  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: 6254  Time=0.00s (2s)  Lock=0.00s (0s)  Rows=1.0 (6254), isucon[isucon]@localhost
  SELECT * FROM livestreams WHERE id = N

Count: 222  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: 448  Time=0.01s (2s)  Lock=0.00s (0s)  Rows=0.5 (224), 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: 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: 225  Time=0.01s (2s)  Lock=0.00s (0s)  Rows=0.0 (0), isudns[isudns]@localhost
  commit

Count: 490  Time=0.00s (2s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  INSERT INTO themes (user_id, dark_mode) VALUES (N, false)

Count: 32  Time=0.06s (1s)  Lock=0.00s (0s)  Rows=0.0 (1), isucon[isucon]@localhost
  SELECT * FROM ng_words WHERE user_id = N AND livestream_id = N ORDER BY created_at DESC

Count: 224  Time=0.01s (1s)  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: 222  Time=0.01s (1s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  DELETE FROM icons WHERE user_id = N

Count: 25  Time=0.06s (1s)  Lock=0.00s (0s)  Rows=1.2 (30), isucon[isucon]@localhost
  SELECT * FROM ng_words WHERE livestream_id = N

Count: 2591  Time=0.00s (1s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  ROLLBACK

Count: 2816  Time=0.00s (1s)  Lock=0.00s (0s)  Rows=1.0 (2815), isucon[isucon]@localhost
  SELECT * FROM users WHERE name = 'S'

Count: 281  Time=0.00s (1s)  Lock=0.00s (0s)  Rows=6.7 (1884), isucon[isucon]@localhost
  SELECT * FROM livecomments WHERE livestream_id = N ORDER BY created_at DESC

Count: 5080  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  START TRANSACTION

Count: 318  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=5.5 (1758), isucon[isucon]@localhost
  SELECT * FROM reactions WHERE livestream_id = N ORDER BY created_at DESC

Count: 225  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (225), isudns[isudns]@localhost
hedwig100 / isucon13

sql1436 #13
