感想戦: Add descending index `popularity DESC`

Before:

# Time: 2020-09-13T03:37:14.660248Z
# User@Host: root[root] @ localhost []  Id:   520
# Query_time: 0.034722  Lock_time: 0.000161 Rows_sent: 10  Rows_examined: 6888
SET timestamp=1599968234;
SELECT SQL_CALC_FOUND_ROWS * FROM estate WHERE rent_t = 0 ORDER BY popularity DESC, id ASC LIMIT 10 OFFSET 0;

# Time: 2020-09-13T03:37:20.714416Z
# User@Host: root[root] @ localhost []  Id:   520
# Query_time: 0.014294  Lock_time: 0.000213 Rows_sent: 10  Rows_examined: 3454
SET timestamp=1599968240;
SELECT * FROM estate WHERE rent_t = 0 ORDER BY popularity DESC, id ASC LIMIT 10 OFFSET 0;

root@localhost [isuumo] > EXPLAIN SELECT * FROM estate WHERE rent_t = 0 ORDER BY popularity DESC, id ASC LIMIT 10 OFFSET 0;
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+----------------+
| id | select_type | table  | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra          |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+----------------+
|  1 | SIMPLE      | estate | NULL       | ref  | idx_rent_t    | idx_rent_t | 5       | const | 3444 |   100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

After:

# Time: 2020-09-13T03:44:26.615953Z
# User@Host: root[root] @ localhost []  Id:   520
# Query_time: 0.024876  Lock_time: 0.000387 Rows_sent: 10  Rows_examined: 3444
SET timestamp=1599968666;
SELECT SQL_CALC_FOUND_ROWS * FROM estate WHERE rent_t = 0 ORDER BY popularity DESC, id ASC LIMIT 10 OFFSET 0;

# Time: 2020-09-13T03:44:42.873486Z
# User@Host: root[root] @ localhost []  Id:   520
# Query_time: 0.006961  Lock_time: 0.000201 Rows_sent: 10  Rows_examined: 10
SET timestamp=1599968682;
SELECT * FROM estate WHERE rent_t = 0 ORDER BY popularity DESC, id ASC LIMIT 10 OFFSET 0;

root@localhost [isuumo] > EXPLAIN SELECT * FROM estate WHERE rent_t = 0 ORDER BY popularity DESC, id ASC LIMIT 10 OFFSET 0;
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | estate | NULL       | ref  | idx_rent_t_popularity | idx_rent_t_popularity | 5       | const | 3444 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Rows_examined: 3454 -> 10

COUNT(*)自体は遅くないのでSQL_CALC_FOUND_ROWS狙わずにORDER BY LIMIT optimization狙いでCOUNT(*)と合わせたほうが速い。

# Time: 2020-09-13T03:47:25.704713Z
# User@Host: root[root] @ localhost []  Id:   520
# Query_time: 0.001760  Lock_time: 0.000152 Rows_sent: 1  Rows_examined: 3444
SET timestamp=1599968845;
SELECT COUNT(*) FROM estate WHERE rent_t = 0;

いちおうMySQL 5.7でもpopularityを反転させて昇順ソートの揃えれば同じ効率のインデックスにはできる。

UPDATE estate SET popularity = -popularity

soudai / isucon10-qualify

感想戦: Add descending index `popularity DESC` #14