visit_historyに対するindex追加

[pinkumohikan]

refs #1

# Query 1: 16.22 QPS, 1.68x concurrency, ID 0x676347F321DB8BC7FCB05D4948FC2248 at byte 5276415
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.10
# Time range: 2022-07-23T01:39:36 to 2022-07-23T01:40:52
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3    1233
# Exec time     59    128s   146us   597ms   104ms   323ms   102ms    68ms
# Lock time      0     2ms     1us    61us     1us     2us     2us     1us
# Rows sent     97 100.55k       0     199   83.51  174.84   53.46   72.65
# Rows examine  88  23.22M       0  49.19k  19.28k  44.45k  13.52k  14.47k
# Query size    11 172.18k     142     144  143.00  136.99    0.75  136.99
# String:
# Databases    isuports
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us  #####
#   1ms  ###########
#  10ms  ################################################################
# 100ms  ###############################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuports` LIKE 'visit_history'\G
#    SHOW CREATE TABLE `isuports`.`visit_history`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT player_id, MIN(created_at) AS min_created_at FROM visit_history WHERE tenant_id = 45 AND competition_id = '4d1b6fea5' GROUP BY player_id\G

に対するチューニング

[pinkumohikan]

mysql> explain SELECT player_id, MIN(created_at) AS min_created_at FROM visit_history WHERE tenant_id = 45 AND competition_id = '4d1b6fea5' GROUP BY player_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: visit_history
   partitions: NULL
         type: ref
possible_keys: tenant_id_idx
          key: tenant_id_idx
      key_len: 8
          ref: const
         rows: 102942
     filtered: 10.00
        Extra: Using where; Using temporary
1 row in set, 1 warning (0.00 sec)

mysql> ALTER TABLE visit_history ADD INDEX (tenant_id, competition_id, player_id, created_at);
Query OK, 0 rows affected (35.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain SELECT player_id, MIN(created_at) AS min_created_at FROM visit_history WHERE tenant_id = 45 AND competition_id = '4d1b6fea5' GROUP BY player_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: visit_history
   partitions: NULL
         type: ref
possible_keys: tenant_id_idx,tenant_id
          key: tenant_id
      key_len: 1030
          ref: const,const
         rows: 3146
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

[pinkumohikan]

https://portal.isucon.net/contestant/benchmark_jobs/1597

Score: 3743 Score Breakdown: base=3743, deduction=0

[pinkumohikan]

01:50:35.982227 leaderboardの表示に1秒以上かかったため1人の参加者が離脱しました。