ISUCON7解いてみた

[mysqld]
slow_query_log=1
slow_query_log_file=’/var/log/my/mysql-slow.log’
long_query_time=1

sudo pt-query-digest /var/log/mysql/mysql-slow.sql

# 3.6s user time, 10ms system time, 32.13M rss, 85.17M vsz
# Current date: Tue Oct 17 17:58:08 2023
# Hostname: ip-172-31-46-74
# Files: /var/log/mysql/mysql-slow.sql
# Overall: 24.90k total, 28 unique, 22.11 QPS, 0.29x concurrency _________
# Time range: 2023-10-17T17:38:45 to 2023-10-17T17:57:31
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           331s     2us   820ms    13ms    10ms    65ms   119us
# Lock time             1s       0    14ms    42us    76us   198us    31us
# Rows sent         47.36k       0     100    1.95    0.99    9.23    0.99
# Rows examine      69.26M       0   9.77k   2.85k   9.33k   4.21k    0.99
# Query size         2.13M      20 373.87k   89.55   72.65   2.68k   62.76

# Profile
# Rank Query ID                            Response time  Calls R/Call V/M
# ==== =================================== ============== ===== ====== ===
#    1 0x086EF2D69E0CA10C16A13978F3B7FCFA  300.4205 90.8%   842 0.3568  0.02 SELECT image
#    2 0xB36405B8C0D2F0C74866C96BA295C56E   24.8958  7.5%  7116 0.0035  0.00 SELECT message
# MISC 0xMISC                                5.6535  1.7% 16941 0.0003   0.0 <26 ITEMS>

# Query 1: 3.07 QPS, 1.10x concurrency, ID 0x086EF2D69E0CA10C16A13978F3B7FCFA at byte 811325
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02
# Time range: 2023-10-17T17:38:50 to 2023-10-17T17:43:24
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3     842
# Exec time     90    300s   221ms   820ms   357ms   501ms    83ms   323ms
# Lock time      5    62ms    35us   611us    73us    93us    28us    73us
# Rows sent     27  13.15k       1      25   16.00   22.53    3.81   15.25
# Rows examine   1 824.96k    1001    1004 1003.27  964.41    0.00  964.41
# Query size     2  64.89k      46      79   78.92   76.28    1.54   76.28
# String:
# Databases    isubata
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isubata` LIKE 'image'\G
#    SHOW CREATE TABLE `isubata`.`image`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM image WHERE name = '78a9228a393eb2621f346fc6a5e099d5bc373f76.png'\G

# Query 2: 6.35 QPS, 0.02x concurrency, ID 0xB36405B8C0D2F0C74866C96BA295C56E at byte 4153261
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-10-17T17:38:51 to 2023-10-17T17:57:31
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         28    7116
# Exec time      7     25s     2ms    46ms     3ms    10ms     3ms     2ms
# Lock time     23   249ms    22us     2ms    34us    49us    26us    30us
# Rows sent     14   6.95k       1       1       1       1       0       1
# Rows examine  97  67.87M   9.77k   9.77k   9.77k   9.33k       0   9.33k
# Query size    18 409.39k      58      60   58.91   56.92    0.28   56.92
# String:
# Databases    isubata
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms  ###
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isubata` LIKE 'message'\G
#    SHOW CREATE TABLE `isubata`.`message`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT COUNT(*) as cnt FROM message WHERE channel_id = '71'\G

$app->get('/message', function (Request $request, Response $response) {
    // userIDの取得
    $userId = FigRequestCookies::get($request, 'user_id')->getValue();
    if (!$userId) {
        return $response->withStatus(403);
    }
    // channelIDの取得
    $channelId = $request->getParam('channel_id');
    // lastMessageIdの取得
    $lastMessageId = $request->getParam('last_message_id');

    $dbh = getPDO();
    $stmt = $dbh->prepare(
        "SELECT * ".
        "FROM message ".
        "WHERE id > ? AND channel_id = ? ORDER BY id DESC LIMIT 100"
    );

alter table message add index channel_id_idx(channel_id);