kirikiriyamama / isucon10-qualify Goto Github PK

mysql> explain SELECT * FROM estate WHERE (door_width >= '60' AND door_height >= '40') OR (door_width >= '60' AND door_height >= '157') OR (door_width >= '40' AND door_height >= '60') OR (door_width >= '40' AND door_height >= '157') OR (door_width >= '157' AND door_height >= '60') OR (door_width >= '157' AND door_height >= '40') ORDER BY popularity DESC, id ASC LIMIT 20;
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                       |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
|  1 | SIMPLE      | estate | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 28732 |    50.67 | Using where; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

ALTER TABLE estate ADD desc_popularity INTEGER GENERATED ALWAYS AS (estate.popularity * (-1)) VIRTUAL;

explain SELECT * FROM estate WHERE (door_width >= '60' AND door_height >= '40') OR (door_width >= '60' AND door_height >= '157') OR (door_width >= '40' AND door_height >= '60') OR (door_width >= '40' AND door_height >= '157') OR (door_width >= '157' AND door_height >= '60') OR (door_width >= '157' AND door_height >= '40') ORDER BY desc_popularity ASC, id ASC LIMIT 20;
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                       |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
|  1 | SIMPLE      | estate | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 28601 |    50.67 | Using where; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
1 row in set, 1 warning (0.01 sec)

sudo pt-query-digest /var/log/mysql/slow.log --resume /tmp/slow.pos

# A software update is available:
# Resuming /var/log/mysql/slow.log from offset 0 because resume file /var/log/mysql/slow.log does not exist (file size: 3671)...

# Saved resume file stop_offset 3671 to /tmp/slow.pos

# 240ms user time, 30ms system time, 41.45M rss, 127.38M vsz
# Current date: Sat Aug 14 02:44:30 2021
# Hostname: ip-172-31-126-203
# Files: /var/log/mysql/slow.log
# Overall: 12 total, 4 unique, 0.05 QPS, 0.00x concurrency _______________
# Time range: 2021-08-14T02:28:53 to 2021-08-14T02:33:00
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          340ms   991us    75ms    28ms    51ms    18ms    23ms
# Lock time           15ms   101us    10ms     1ms     3ms     3ms   152us
# Rows sent            225       5      20   18.75   19.46    4.01   19.46
# Rows examine     322.49k       5  29.32k  26.87k  28.66k   7.92k  28.66k
# Query size          1011      24     357   84.25   69.19   80.56   69.19

# Profile
# Rank Query ID                            Response time Calls R/Call V/M 
# ==== =================================== ============= ===== ====== ====
#    1 0xF8D9C0871992B1AF9C572B62B2210476   0.1742 51.2%     5 0.0348  0.01 SELECT estate
#    2 0x1592B6A6C5962A79A3BBB20FFD7BCE7C   0.1481 43.5%     5 0.0296  0.00 SELECT chair
#    3 0x3D7F9AD8D0B5ECFA387A1968AED87BF9   0.0171  5.0%     1 0.0171  0.00 SELECT estate
# MISC 0xMISC                               0.0010  0.3%     1 0.0010   0.0 <1 ITEMS>

# Query 1: 0.02 QPS, 0.00x concurrency, ID 0xF8D9C0871992B1AF9C572B62B2210476 at byte 469
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2021-08-14T02:28:53 to 2021-08-14T02:33:00
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         41       5
# Exec time     51   174ms    22ms    75ms    35ms    75ms    20ms    24ms
# Lock time     70    11ms   101us    10ms     2ms    10ms     4ms   144us
# Rows sent     44     100      20      20      20      20       0      20
# Rows examine  45 146.58k  29.32k  29.32k  29.32k  29.32k       0  29.32k
# Query size    27     275      55      55      55      55       0      55
# String:
# Databases    isuumo
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ################################################################
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'\G
#    SHOW CREATE TABLE `isuumo`.`estate`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM estate ORDER BY rent ASC, id ASC LIMIT 20\G

# Query 2: 0.02 QPS, 0.00x concurrency, ID 0x1592B6A6C5962A79A3BBB20FFD7BCE7C at byte 0
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2021-08-14T02:28:53 to 2021-08-14T02:33:00
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         41       5
# Exec time     43   148ms    21ms    53ms    30ms    51ms    11ms    23ms
# Lock time     26     4ms   123us     4ms   813us     3ms     1ms   131us
# Rows sent     44     100      20      20      20      20       0      20
# Rows examine  45 146.58k  29.32k  29.32k  29.32k  29.32k       0  29.32k
# Query size    35     355      71      71      71      71       0      71
# String:
# Databases    isuumo
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ################################################################
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'\G
#    SHOW CREATE TABLE `isuumo`.`chair`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM chair WHERE stock > 0 ORDER BY price ASC, id ASC LIMIT 20\G

# Query 3: 0 QPS, 0x concurrency, ID 0x3D7F9AD8D0B5ECFA387A1968AED87BF9 at byte 1271
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2021-08-14T02:31:06
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          8       1
# Exec time      5    17ms    17ms    17ms    17ms    17ms       0    17ms
# Lock time      1   153us   153us   153us   153us   153us       0   153us
# Rows sent      8      20      20      20      20      20       0      20
# Rows examine   9  29.32k  29.32k  29.32k  29.32k  29.32k       0  29.32k
# Query size    35     357     357     357     357     357       0     357
# String:
# Databases    isuumo
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ################################################################
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'\G
#    SHOW CREATE TABLE `isuumo`.`estate`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM estate WHERE (door_width >= '60' AND door_height >= '40') OR (door_width >= '60' AND door_height >= '157') OR (door_width >= '40' AND door_height >= '60') OR (door_width >= '40' AND door_height >= '157') OR (door_width >= '157' AND door_height >= '60') OR (door_width >= '157' AND door_height >= '40') ORDER BY popularity DESC, id ASC LIMIT 20\G