search

k5342 / isucon10-qualify

0 stars 0 forks source link

追加で index を貼る #16

Closed k5342 closed 4 years ago

k5342 commented 4 years ago

14 で激重クエリ君が分かったので関連するものに対してインデックスを貼る

以下追加してくださーい

indexes

SELECT * FROM chair WHERE stock > 0 ORDER BY price ASC, id ASC LIMIT 20;

SELECT * FROM estate WHERE (door_width >= 54 AND door_height >= 171) OR (door_width >= 54 AND door_height >= 90) OR (door_width >= 171 AND door_height >= 54) OR (door_width >= 171 AND door_height >= 90) OR (door_width >= 90 AND door_height >= 54) OR (door_width >= 90 AND door_height >= 171) ORDER BY popularity DESC, id ASC LIMIT 20;

SELECT * FROM estate ORDER BY rent ASC, id ASC LIMIT 20;

SELECT * FROM estate WHERE rent >= 50000 AND rent < 100000 ORDER BY popularity DESC, id ASC LIMIT 25 OFFSET 0;

SELECT * FROM chair WHERE kind = '座椅子' AND stock > 0 ORDER BY popularity DESC, id ASC LIMIT 25 OFFSET 100;

Pelkira commented 4 years ago 
Top 20 Sort By Count
Count    Total      Mean    Stddev    Min  P50.0  P90.0  P95.0  P99.0    Max   2xx  3xx  4xx  5xx  Request
 1299  150.965  0.116216  0.075617  0.000  0.115  0.205  0.226  0.267  0.842  1297    0    2    0  searchEstates
  892  127.155  0.142550  0.076404  0.000  0.144  0.228  0.243  0.287  0.613   889    0    3    0  searchChairs
  707    3.149  0.004454  0.024829  0.000  0.001  0.010  0.016  0.033  0.532   706    0    1    0  getEstateDetail
  437   36.966  0.084590  0.034730  0.025  0.080  0.122  0.141  0.200  0.440   435    0    2    0  GET /api/estate/low_priced HTTP/1.1
  437   39.764  0.090993  0.052845  0.030  0.085  0.123  0.132  0.203  0.840   435    0    2    0  GET /api/chair/low_priced HTTP/1.1
  392    0.454  0.001158  0.003091  0.000  0.001  0.002  0.004  0.023  0.035   392    0    0    0  postEstateRequestDocument
  259    2.572  0.009931  0.028181  0.000  0.004  0.021  0.031  0.056  0.364   257    0    2    0  getChairDetail
  255   32.218  0.126345  0.062288  0.066  0.114  0.166  0.191  0.513  0.674   255    0    0    0  searchRecommendedEstateWithChair
  126    4.561  0.036198  0.162209  0.003  0.007  0.029  0.039  1.107  1.112   126    0    0    0  buyChair
  126   39.709  0.315151  0.442317  0.000  0.107  0.871  1.244  2.000  2.000   122    0    4    0  POST /api/estate/nazotte HTTP/1.1
    1    0.289  0.289000  0.000000  0.289  0.289  0.289  0.289  0.289  0.289     1    0    0    0  POST /api/chair HTTP/1.1
    1    4.906  4.906000  0.000000  4.906  4.906  4.906  4.906  4.906  4.906     1    0    0    0  POST /initialize HTTP/1.1
    1    0.331  0.331000  0.000000  0.331  0.331  0.331  0.331  0.331  0.331     1    0    0    0  POST /api/estate HTTP/1.1
k5342 commented 4 years ago

hatta