pt-query-digest result

gistfile1.txt

# 310ms user time, 10ms system time, 30.65M rss, 98.40M vsz
# Current date: Sun Sep 27 15:37:00 2015
# Hostname: isucon01
# Files: /var/lib/mysql/mysqld-slow.log
# Overall: 334 total, 17 unique, 0.48 QPS, 0.16x concurrency _____________
# Time range: 2015-09-27 15:23:56 to 15:35:33
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           114s    55ms      4s   343ms   945ms   354ms   253ms
# Lock time          295ms    30us    98ms   883us   159us     7ms    49us
# Rows sent         38.21k       0   4.88k  117.15  102.22  638.13    9.83
# Rows examine       1.03M       5 491.61k   3.15k  833.10  37.45k   20.43
# Query size        57.67k      31     821  176.80  246.02  104.63  143.84

# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ===============
#    1 0x75B7218DD44BDBC5 27.6810 24.2%    63 0.4394  0.14 SELECT relations
#    2 0x492C7CAB538DE80D 22.5882 19.7%    53 0.4262  0.25 SELECT footprints
#    3 0xA44D49C56E635213 14.5630 12.7%    63 0.2312  0.02 SELECT entries
#    4 0x57EBD74C3B600DB8 14.0403 12.3%    40 0.3510  0.02 SELECT entries
#    5 0xDC67C8CE8419EE5B 12.8485 11.2%     7 1.8355  0.19 SELECT entries
#    6 0xC78A350801004125  6.4956  5.7%    44 0.1476  0.01 SELECT comments
#    7 0xACA994DF02CA5667  5.8272  5.1%    17 0.3428  0.02 SELECT entries
#    8 0xCD5232DE02D38548  3.6298  3.2%     2 1.8149  3.18 SELECT footprints
#    9 0xEE72BAA1F364AF3A  2.6306  2.3%    21 0.1253  0.00 SELECT entries
# MISC 0xMISC              4.1301  3.6%    24 0.1721   0.0 <8 ITEMS>

SELECT /* /home/isucon/deploy/lib/Isucon5/Web.pm line 118 */ another FROM relations WHERE one = '2646' ORDER BY created_at DESC\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: relations
#    partitions: NULL
#          type: ref
# possible_keys: friendship,one
#           key: one
#       key_len: 4
#           ref: const
#          rows: 104
#         Extra: Using where

SELECT /* /home/isucon/deploy/lib/Isucon5/Web.pm line 277 */ user_id, owner_id, DATE(created_at) AS date, MAX(created_at) as updated
FROM footprints
WHERE user_id = '1189'
GROUP BY user_id, owner_id, DATE(created_at)
ORDER BY updated DESC
LIMIT 10\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: footprints
#    partitions: NULL
#          type: ref
# possible_keys: user_id
#           key: user_id
#       key_len: 4
#           ref: const
#          rows: 107
#         Extra: Using where; Using temporary; Using filesort

SELECT /* /home/isucon/deploy/lib/Isucon5/Web.pm line 277 */ user_id, owner_id, DATE(created_at) AS date, MAX(created_at) as updated
FROM footprints
WHERE user_id = '1189'
GROUP BY user_id, owner_id, DATE(created_at)
ORDER BY updated DESC
LIMIT 10\G
# *************************** 1. row ***************************
#            id: 1
#   select_type: SIMPLE
#         table: footprints
#    partitions: NULL
#          type: ref
# possible_keys: user_id
#           key: user_id
#       key_len: 4
#           ref: const
#          rows: 107
#         Extra: Using where; Using temporary; Using filesort