Optimize get '/api/recommended_estate/:id'

[kamipo]

Before:

# Time: 2020-09-13T04:08:27.718315Z
# User@Host: root[root] @ localhost []  Id:   520
# Query_time: 0.036697  Lock_time: 0.000160 Rows_sent: 20  Rows_examined: 29520
SET timestamp=1599970107;
SELECT * FROM estate WHERE (door_width >= 31 AND door_height >= 143) OR (door_width >= 143 AND door_height >= 31) ORDER BY popularity DESC, id ASC LIMIT 20;

root@localhost [isuumo] > EXPLAIN SELECT * FROM estate WHERE (door_width >= 31 AND door_height >= 143) OR (door_width >= 143 AND door_height >= 31) 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 | 28691 |    20.98 | Using where; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

After:

# Time: 2020-09-13T04:18:40.796750Z
# User@Host: root[root] @ localhost []  Id:   520
# Query_time: 0.001144  Lock_time: 0.000247 Rows_sent: 20  Rows_examined: 29
SET timestamp=1599970720;
SELECT * FROM estate WHERE (door_width >= 31 AND door_height >= 143) OR (door_width >= 143 AND door_height >= 31) ORDER BY popularity DESC, id ASC LIMIT 20;

root@localhost [isuumo] > EXPLAIN SELECT * FROM estate WHERE (door_width >= 31 AND door_height >= 143) OR (door_width >= 143 AND door_height >= 31) 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       | index | NULL          | idx_popularity | 4       | NULL |   20 |    20.98 | Using where |
+----+-------------+--------+------------+-------+---------------+----------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

Rows_examined: 29520 -> 29 Query_time: 0.036697 -> 0.001144

もともとno indexのフルスキャン、クエリを書き換えても速くはならない。

popularityのDESC indexかpopularityを昇順に変換してASC indexでORDER BY LIMIT optimization狙いで20件拾いにいくのが一番効率がいい。

[kamipo]

フルスキャンは濡れ衣だった。

https://github.com/soudai/isucon10-qualify/blob/1be06d2540eb94244596e9a7b541f7c4caf4c14f/webapp/mysql/db/0_Schema.sql#L67-L68

けど Rows_examined: 29 を狙えるpopularityのindexのほうがよさそう。

[kamipo]

このインデックス椅子か！やっぱフルスキャンだった

[kamipo]

こっちか。

https://github.com/soudai/isucon10-qualify/blob/1be06d2540eb94244596e9a7b541f7c4caf4c14f/webapp/mysql/db/0_Schema.sql#L28-L29

けど Rows_examined: 29 以下略