Skip to content
This repository has been archived by the owner on Feb 15, 2024. It is now read-only.

DB queryログ解析 #16

Open
sota1235 opened this issue Sep 12, 2020 · 1 comment
Open

DB queryログ解析 #16

sota1235 opened this issue Sep 12, 2020 · 1 comment

Comments

@sota1235
Copy link
Owner 


# 760ms user time, 30ms system time, 30.88M rss, 4.68G vsz
# Current date: Sat Sep 12 17:45:27 2020
# Hostname: MacBook-Pro.local
# Files: mysql-slow.20201505.log
# Overall: 4.63k total, 68 unique, 75.98 QPS, 6.68x concurrency __________
# Time range: 2020-09-12T06:00:09 to 2020-09-12T06:01:10
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           408s    23ms   302ms    88ms   141ms    30ms    78ms
# Lock time          205ms     7us    16ms    44us    28us   360us    13us
# Rows sent         82.44k       0   1.46k   18.21   24.84   52.47   19.46
# Rows examine     132.73M  28.81k  30.76k  29.32k  28.66k   56.97  28.66k
# Query size       441.76k      46     341   97.60  329.68   62.72   76.28

# Profile
# Rank Query ID                       Response time Calls R/Call V/M   Ite
# ==== ============================== ============= ===== ====== ===== ===
#    1 0x1592B6A6C5962A79A3BBB20FF... 40.3430  9.9%   377 0.1070  0.01 SELECT chair
#    2 0xF8D9C0871992B1AF9C572B62B... 31.5254  7.7%   377 0.0836  0.01 SELECT estate
#    3 0x3D7F9AD8D0B5ECFA387A1968A... 29.9158  7.3%   251 0.1192  0.01 SELECT estate
#    4 0x07E5ED76C7A93E48E37BD5D8F... 29.4927  7.2%   317 0.0930  0.01 SELECT estate
#    5 0x7239C60E57855697302221084... 23.9203  5.9%   317 0.0755  0.01 SELECT estate
#    6 0xDAF418A1CBFF50D9A9A4F3806... 15.1817  3.7%   156 0.0973  0.01 SELECT chair
#    7 0x5DE2A94FD483D5087F578E616... 14.4867  3.6%   177 0.0818  0.01 SELECT estate
#    8 0x4A8E12431038633B5ADF2C461... 13.9658  3.4%   126 0.1108  0.01 SELECT chair
#    9 0x4E8EAE12D58B73C2F3EFAD490... 12.6417  3.1%   156 0.0810  0.01 SELECT chair
#   10 0xBF1444BD9A8EAD17F291508A5... 12.3061  3.0%   177 0.0695  0.01 SELECT estate
#   11 0x644E34B4F9AC414D981076932... 11.9216  2.9%   153 0.0779  0.01 SELECT estate
#   12 0x1C8D962590CDA8C931953C185... 11.4224  2.8%   126 0.0907  0.01 SELECT chair
#   13 0x55402B93A43729A085DF0F23B... 10.7277  2.6%    84 0.1277  0.01 SELECT estate
#   14 0x4CD8BC51141C351DC73BFA734...  9.6834  2.4%   153 0.0633  0.00 SELECT estate
#   15 0x2F6A8881D435EE788EED46376...  8.6171  2.1%   105 0.0821  0.01 SELECT estate
#   16 0xE5EB3FC758DE6E117FEEE42B5...  8.1968  2.0%    78 0.1051  0.01 SELECT chair
#   17 0x747A5415F890FCD0F418F8958...  7.3985  1.8%    89 0.0831  0.01 SELECT estate
#   18 0x3D16910D1C54800461F4988BE...  7.2243  1.8%    75 0.0963  0.01 SELECT chair
#   19 0xED731470FE91D25E31E27AD9C...  7.1508  1.8%   105 0.0681  0.01 SELECT estate
#   20 0x85EDDA9F02FE0A0E65DBB57A0...  6.4864  1.6%    78 0.0832  0.01 SELECT chair
# MISC 0xMISC                         95.0932 23.3%  1158 0.0821   0.0 <48 ITEMS>

# Query 1: 6.18 QPS, 0.66x concurrency, ID 0x1592B6A6C5962A79A3BBB20FFD7BCE7C at byte 9271
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2020-09-12T06:00:09 to 2020-09-12T06:01:10
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          8     377
# Exec time      9     40s    54ms   217ms   107ms   155ms    28ms   100ms
# Lock time      5    11ms     9us     2ms    29us    23us   127us    13us
# Rows sent      8   7.36k      20      20      20      20       0      20
# Rows examine   8  10.79M  28.83k  29.32k  29.32k  28.66k       0  28.66k
# Query size     5  26.14k      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 2: 6.18 QPS, 0.52x concurrency, ID 0xF8D9C0871992B1AF9C572B62B2210476 at byte 0
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2020-09-12T06:00:09 to 2020-09-12T06:01:10
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          8     377
# Exec time      7     32s    43ms   195ms    84ms   122ms    24ms    78ms
# Lock time     24    51ms     8us     8ms   134us   348us   614us    12us
# Rows sent      8   7.36k      20      20      20      20       0      20
# Rows examine   8  10.79M  28.83k  29.32k  29.32k  28.66k       0  28.66k
# Query size     4  20.25k      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 3: 4.11 QPS, 0.49x concurrency, ID 0x3D7F9AD8D0B5ECFA387A1968AED87BF9 at byte 3642
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2020-09-12T06:00:09 to 2020-09-12T06:01:10
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          5     251
# Exec time      7     30s    58ms   302ms   119ms   171ms    35ms   105ms
# Lock time      2     5ms    18us   120us    21us    31us     8us    18us
# Rows sent      5   4.90k      20      20      20      20       0      20
# Rows examine   5   7.18M  28.83k  29.32k  29.31k  28.66k       0  28.66k
# Query size    18  82.44k     329     341  336.33  329.68    3.21  329.68
# 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 >= 45 AND door_height >= 180) OR (door_width >= 45 AND door_height >= 183) OR (door_width >= 180 AND door_height >= 45) OR (door_width >= 180 AND door_height >= 183) OR (door_width >= 183 AND door_height >= 45) OR (door_width >= 183 AND door_height >= 180) ORDER BY popularity DESC, id ASC LIMIT 20\G

# Query 4: 5.37 QPS, 0.50x concurrency, ID 0x07E5ED76C7A93E48E37BD5D8F0FF2FDE at byte 939072
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2020-09-12T06:00:11 to 2020-09-12T06:01:10
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          6     317
# Exec time      7     29s    40ms   193ms    93ms   141ms    26ms    82ms
# Lock time      5    11ms    10us     3ms    36us    25us   218us    14us
# Rows sent      9   7.74k      25      25      25      25       0      25
# Rows examine   6   9.09M  29.32k  29.42k  29.35k  28.66k    0.00  28.66k
# Query size     7  34.09k     109     112  110.12  107.34    0.00  107.34
# 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 rent >= 50000 AND rent < 100000 ORDER BY popularity DESC, id ASC LIMIT 25 OFFSET 0\G

# Query 5: 5.37 QPS, 0.41x concurrency, ID 0x7239C60E5785569730222108492FF4F7 at byte 1252131
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2020-09-12T06:00:11 to 2020-09-12T06:01:10
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          6     317
# Exec time      5     24s    29ms   155ms    75ms   116ms    22ms    68ms
# Lock time      5    10ms     7us     3ms    32us    23us   197us    12us
# Rows sent      0     317       1       1       1       1       0       1
# Rows examine   6   9.07M  29.30k  29.30k  29.30k  29.30k       0  29.30k
# Query size     4  20.27k      65      66   65.49   65.89    0.50   62.76
# 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 COUNT(*) FROM estate WHERE rent >= 100000 AND rent < 150000\G

# Query 6: 2.60 QPS, 0.25x concurrency, ID 0xDAF418A1CBFF50D9A9A4F380692DE1C8 at byte 1405941
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2020-09-12T06:00:10 to 2020-09-12T06:01:10
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3     156
# Exec time      3     15s    51ms   191ms    97ms   141ms    27ms    87ms
# Lock time      3     7ms    10us     4ms    45us    27us   318us    15us
# Rows sent      4   3.81k      25      25      25      25       0      25
# Rows examine   3   4.47M  29.32k  29.42k  29.35k  28.66k       0  28.66k
# Query size     4  18.61k     121     125  122.19  118.34    1.04  118.34
# 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 price >= 12000 AND price < 15000 AND stock > 0 ORDER BY popularity DESC, id ASC LIMIT 25 OFFSET 0\G

# Query 7: 2.95 QPS, 0.24x concurrency, ID 0x5DE2A94FD483D5087F578E6167724DBF at byte 980671
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2020-09-12T06:00:10 to 2020-09-12T06:01:10
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3     177
# Exec time      3     14s    32ms   143ms    82ms   122ms    24ms    75ms
# Lock time      3     6ms     9us     2ms    35us    28us   184us    13us
# Rows sent      5   4.32k      25      25      25      25       0      25
# Rows examine   3   5.07M  29.32k  29.42k  29.36k  28.66k    0.00  28.66k
# Query size     3  15.68k      90      92   90.70   88.31       0   88.31
# 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 rent < 50000 ORDER BY popularity DESC, id ASC LIMIT 25 OFFSET 75\G

# Query 8: 2.29 QPS, 0.25x concurrency, ID 0x4A8E12431038633B5ADF2C461C6F48FE at byte 1251305
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2020-09-12T06:00:14 to 2020-09-12T06:01:09
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2     126
# Exec time      3     14s    65ms   195ms   111ms   163ms    31ms    95ms
# Lock time      2     5ms    13us     1ms    39us    31us   142us    16us
# Rows sent      3   3.08k      25      25      25      25       0      25
# Rows examine   2   3.61M  29.32k  29.42k  29.35k  28.66k       0  28.66k
# Query size     3  14.56k     109     126  118.33  124.25    5.70  118.34
# 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 kind = 'ハンモック' AND stock > 0 ORDER BY popularity DESC, id ASC LIMIT 25 OFFSET 0\G

# Query 9: 2.60 QPS, 0.21x concurrency, ID 0x4E8EAE12D58B73C2F3EFAD4903F0402F at byte 203830
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2020-09-12T06:00:10 to 2020-09-12T06:01:10
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3     156
# Exec time      3     13s    45ms   197ms    81ms   128ms    24ms    71ms
# Lock time      1     3ms     8us   462us    18us    23us    37us    12us
# Rows sent      0     156       1       1       1       1       0       1
# Rows examine   3   4.46M  29.30k  29.30k  29.30k  29.30k       0  29.30k
# Query size     2  11.81k      77      79   77.54   76.28       0   76.28
# 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 COUNT(*) FROM chair WHERE price >= 9000 AND price < 12000 AND stock > 0\G

# Query 10: 2.95 QPS, 0.21x concurrency, ID 0xBF1444BD9A8EAD17F291508A52D09114 at byte 1251627
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2020-09-12T06:00:10 to 2020-09-12T06:01:10
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3     177
# Exec time      3     12s    23ms   152ms    70ms   105ms    22ms    65ms
# Lock time      1     4ms     8us     1ms    20us    19us    80us    10us
# Rows sent      0     177       1       1       1       1       0       1
# Rows examine   3   5.06M  29.30k  29.30k  29.30k  29.30k       0  29.30k
# Query size     1   7.95k      46      46      46      46       0      46
# 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 COUNT(*) FROM estate WHERE rent < 50000\G

# Query 11: 2.68 QPS, 0.21x concurrency, ID 0x644E34B4F9AC414D9810769325CA5A5E at byte 1162268
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2020-09-12T06:00:12 to 2020-09-12T06:01:09
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3     153
# Exec time      2     12s    40ms   165ms    78ms   116ms    20ms    71ms
# Lock time      1     3ms    10us   299us    17us    22us    22us    13us
# Rows sent      4   3.74k      25      25      25      25       0      25
# Rows examine   3   4.39M  29.32k  29.42k  29.35k  28.66k       0  28.66k
# Query size     3  13.84k      92      94   92.65   92.72       1   92.72
# 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 rent >= 150000 ORDER BY popularity DESC, id ASC LIMIT 25 OFFSET 100\G

# Query 12: 2.29 QPS, 0.21x concurrency, ID 0x1C8D962590CDA8C931953C185D32A8B5 at byte 204392
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2020-09-12T06:00:14 to 2020-09-12T06:01:09
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2     126
# Exec time      2     11s    49ms   216ms    91ms   128ms    27ms    82ms
# Lock time      4    10ms    10us     8ms    76us    23us   668us    13us
# Rows sent      0     126       1       1       1       1       0       1
# Rows examine   2   3.60M  29.30k  29.30k  29.30k  29.30k       0  29.30k
# Query size     2   9.06k      65      80   73.64   76.28    5.41   76.28
# 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 COUNT(*) FROM chair WHERE kind = 'ゲーミングチェア' AND stock > 0\G

# Query 13: 1.38 QPS, 0.18x concurrency, ID 0x55402B93A43729A085DF0F23BB694539 at byte 979717
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2020-09-12T06:00:09 to 2020-09-12T06:01:10
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1      84
# Exec time      2     11s    77ms   236ms   128ms   189ms    31ms   122ms
# Lock time      3     7ms    19us     4ms    79us    57us   431us    23us
# Rows sent     21  17.86k       0   1.46k  217.75  918.49  327.03   51.63
# Rows examine   1   2.42M  28.81k  30.76k  29.48k  30.09k  405.51  28.66k
# Query size     3  15.99k     192     197  194.90  192.76    1.00  192.76
# 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 latitude <= 35.120566284374455 AND latitude >= 34.39332352463647 AND longitude <= 137.77832867253431 AND longitude >= 137.1037525430152 ORDER BY popularity DESC, id ASC\G

# Query 14: 2.68 QPS, 0.17x concurrency, ID 0x4CD8BC51141C351DC73BFA73427CD78F at byte 810932
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2020-09-12T06:00:12 to 2020-09-12T06:01:09
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3     153
# Exec time      2     10s    29ms   139ms    63ms    95ms    17ms    59ms
# Lock time      8    18ms     9us    16ms   116us    19us     1ms    11us
# Rows sent      0     153       1       1       1       1       0       1
# Rows examine   3   4.38M  29.30k  29.30k  29.30k  29.30k       0  29.30k
# Query size     1   7.17k      48      48      48      48       0      48
# 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 COUNT(*) FROM estate WHERE rent >= 150000\G

# Query 15: 1.91 QPS, 0.16x concurrency, ID 0x2F6A8881D435EE788EED46376BCF2AE3 at byte 1000022
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2020-09-12T06:00:10 to 2020-09-12T06:01:05
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2     105
# Exec time      2      9s    28ms   165ms    82ms   122ms    21ms    75ms
# Lock time      0     2ms    10us    96us    16us    19us     8us    13us
# Rows sent      3   2.56k      25      25      25      25       0      25
# Rows examine   2   3.01M  29.32k  29.42k  29.35k  28.66k    0.00  28.66k
# Query size     2  12.12k     117     120  118.17  118.34    1.50  112.70
# 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_height >= 80 AND door_height < 110 ORDER BY popularity DESC, id ASC LIMIT 25 OFFSET 50\G

# Query 16: 1.37 QPS, 0.14x concurrency, ID 0xE5EB3FC758DE6E117FEEE42B5C7412D2 at byte 941030
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2020-09-12T06:00:11 to 2020-09-12T06:01:08
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1      78
# Exec time      2      8s    58ms   236ms   105ms   163ms    32ms    95ms
# Lock time      1     2ms    12us   317us    28us    42us    43us    17us
# Rows sent      2   1.90k      25      25      25      25       0      25
# Rows examine   1   2.24M  29.32k  29.42k  29.36k  28.66k       0  28.66k
# Query size     1   8.31k     104     115  109.09  112.70    4.64  107.34
# 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 color = '赤' AND stock > 0 ORDER BY popularity DESC, id ASC LIMIT 25 OFFSET 25\G

# Query 17: 1.53 QPS, 0.13x concurrency, ID 0x747A5415F890FCD0F418F8958C3A84C3 at byte 1002516
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2020-09-12T06:00:12 to 2020-09-12T06:01:10
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1      89
# Exec time      1      7s    40ms   163ms    83ms   128ms    23ms    75ms
# Lock time      1     3ms    12us     2ms    39us    36us   182us    14us
# Rows sent      2   2.17k      25      25      25      25       0      25
# Rows examine   1   2.55M  29.32k  29.42k  29.35k  28.66k    0.00  28.66k
# Query size     2  10.11k     115     118  116.29  112.70    0.00  112.70
# 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 >= 110 AND door_width < 150 ORDER BY popularity DESC, id ASC LIMIT 25 OFFSET 50\G

# Query 18: 1.29 QPS, 0.12x concurrency, ID 0x3D16910D1C54800461F4988BE0D150F8 at byte 1239440
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2020-09-12T06:00:11 to 2020-09-12T06:01:09
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1      75
# Exec time      1      7s    51ms   184ms    96ms   141ms    26ms    87ms
# Lock time      1     3ms    14us     2ms    39us    28us   171us    15us
# Rows sent      2   1.83k      25      25      25      25       0      25
# Rows examine   1   2.15M  29.32k  29.42k  29.35k  28.66k       0  28.66k
# Query size     2   8.88k     120     123  121.20  118.34       0  118.34
# 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 height >= 80 AND height < 110 AND stock > 0 ORDER BY popularity DESC, id ASC LIMIT 25 OFFSET 75\G

# Query 19: 1.91 QPS, 0.13x concurrency, ID 0xED731470FE91D25E31E27AD9C15C891F at byte 997128
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2020-09-12T06:00:10 to 2020-09-12T06:01:05
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2     105
# Exec time      1      7s    35ms   147ms    68ms    95ms    19ms    61ms
# Lock time      0     2ms    10us   133us    15us    22us    12us    12us
# Rows sent      0     105       1       1       1       1       0       1
# Rows examine   2   3.00M  29.30k  29.30k  29.30k  29.30k       0  29.30k
# Query size     1   7.54k      73      74   73.51   72.65       0   72.65
# 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 COUNT(*) FROM estate WHERE door_height >= 80 AND door_height < 110\G

# Query 20: 1.39 QPS, 0.12x concurrency, ID 0x85EDDA9F02FE0A0E65DBB57A0B593F83 at byte 203555
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2020-09-12T06:00:11 to 2020-09-12T06:01:07
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1      78
# Exec time      1      6s    37ms   197ms    83ms   128ms    25ms    75ms
# Lock time      0     1ms     9us   171us    17us    23us    17us    13us
# Rows sent      0      78       1       1       1       1       0       1
# Rows examine   1   2.23M  29.30k  29.30k  29.30k  29.30k       0  29.30k
# Query size     1   4.90k      60      69   64.27   65.89    2.93   65.89
# 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 COUNT(*) FROM chair WHERE color = 'ネイビー' AND stock > 0\G
@sota1235
Copy link
Owner Author 

SELECT * FROM estate WHERE (door_width >= 45 AND door_height >= 180) OR (door_width >= 45 AND door_height >= 183) OR (door_width >= 180 AND door_height >= 45) OR (door_width >= 180 AND door_height >= 183) OR (door_width >= 183 AND door_height >= 45) OR (door_width >= 183 AND door_height >= 180) ORDER BY popularity DESC, id ASC LIMIT 20\G

SELECT * FROM chair WHERE kind = 'ハンモック' AND stock > 0 ORDER BY popularity DESC, id ASC LIMIT 25 OFFSET 0\G

SELECT * FROM estate WHERE latitude <= 35.120566284374455 AND latitude >= 34.39332352463647 AND longitude <= 137.77832867253431 AND longitude >= 137.1037525430152 ORDER BY popularity DESC, id ASC\G

SELECT * FROM chair WHERE color = '赤' AND stock > 0 ORDER BY popularity DESC, id ASC LIMIT 25 OFFSET 25\G

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@sota1235