Slow Query一覧 - Githubissues

# mysqldumpslow -s t /tmp/mysql-slow.log

Reading mysql slow query log from /tmp/mysql-slow.log
Count: 10644  Time=0.02s (252s)  Lock=0.00s (1s)  Rows=1.0 (10644), isucon[isucon]@localhost
  SELECT COUNT(*) as cnt FROM message WHERE channel_id = N

Count: 91  Time=2.15s (195s)  Lock=0.00s (0s)  Rows=1.9 (174), isucon[isucon]@localhost
  SELECT * FROM image WHERE name = 'S'

Count: 463  Time=0.08s (34s)  Lock=0.00s (0s)  Rows=7.4 (3430), isucon[isucon]@localhost
  SELECT * FROM message WHERE channel_id = 'S' ORDER BY id DESC LIMIT N OFFSET N

Count: 858  Time=0.03s (29s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  INSERT INTO message (channel_id, user_id, content, created_at) VALUES ('S', N, 'S', NOW())

Count: 207  Time=0.13s (26s)  Lock=0.04s (8s)  Rows=0.0 (0), isucon[isucon]@localhost
  INSERT INTO image (name, data) VALUES ('S', _binary X'S')

Count: 264  Time=0.09s (23s)  Lock=0.00s (0s)  Rows=28.9 (7617), isucon[isucon]@localhost
  SELECT * FROM message WHERE id > 'S' AND channel_id = 'S' ORDER BY id DESC LIMIT N

Count: 11127  Time=0.00s (14s)  Lock=0.00s (0s)  Rows=1.0 (11127), isucon[isucon]@localhost
  SELECT name, display_name, avatar_icon FROM user WHERE id = N

Count: 266  Time=0.04s (11s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  INSERT INTO haveread (user_id, channel_id, message_id, updated_at, created_at)
  VALUES (N, 'S', N, NOW(), NOW())
  ON DUPLICATE KEY UPDATE message_id = N, updated_at = NOW()

Count: 462  Time=0.02s (9s)  Lock=0.00s (0s)  Rows=1.0 (462), isucon[isucon]@localhost
  SELECT COUNT(*) as cnt FROM message WHERE channel_id = 'S'

Count: 206  Time=0.04s (8s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  INSERT INTO user (name, salt, password, display_name, avatar_icon, created_at)
  VALUES ('S', 'S', 'S', 'S', 'S', NOW())

Count: 10717  Time=0.00s (8s)  Lock=0.00s (0s)  Rows=0.0 (107), isucon[isucon]@localhost
  SELECT * FROM haveread WHERE user_id = N AND channel_id = N

Count: 35254  Time=0.00s (6s)  Lock=0.00s (0s)  Rows=3.6 (125603), 2users@localhost
  #

Count: 208  Time=0.03s (5s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  UPDATE user SET avatar_icon = 'S' WHERE id = N

Count: 208  Time=0.02s (3s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  UPDATE user SET display_name = 'S' WHERE id = N

Count: 2202  Time=0.00s (2s)  Lock=0.00s (0s)  Rows=1.0 (2202), isucon[isucon]@localhost
  SELECT * FROM user WHERE id = N

Count: 1087  Time=0.00s (2s)  Lock=0.00s (0s)  Rows=1.0 (1085), isucon[isucon]@localhost
  SELECT * FROM user WHERE name = 'S'

Count: 938  Time=0.00s (2s)  Lock=0.00s (0s)  Rows=100.0 (93800), isucon[isucon]@localhost
  SELECT * FROM channel ORDER BY id

Count: 107  Time=0.02s (2s)  Lock=0.00s (0s)  Rows=1.0 (107), isucon[isucon]@localhost
  SELECT COUNT(*) as cnt FROM message WHERE channel_id = N AND N < id

Count: 60  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=100.0 (6000), isucon[isucon]@localhost
  SELECT id FROM channel

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  set global slow_query_log = ON

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  set global long_query_time = N

Count: 35253  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  administrator command: Ping

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  administrator command: Quit

explain

mysql> explain select * from message where user_id =1;
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | message | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10877 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from message where channel_id =1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    24
Current database: isubata

+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | message | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10976 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

takoyakisoba / isucon-practice-20210424

Slow Query一覧 #11