Add a missing key on DrydockLease
ClosedPublic
Actions

Authored by epriestley on Oct 26 2017, 8:20 PM.

Details

Reviewers

amckinley

Commits

rPd1b4c9f50b99: Add a missing key on DrydockLease

Summary

Depends on D18734. See PHI176. We run this query on the main Drydock lease web UI, among other places. There is currently no status key which can satisfy it.

Test Plan

Viewed Drydock lease page to get the query.

Ran explain SELECT * FROM `drydock_lease` WHERE (status IN ('pending', 'acquired', 'active')) ORDER BY `id` DESC LIMIT 101; before and after the change.

I don't have a ton of leases locally so the un-key'd EXPLAIN isn't that bad, but still shows that we're getting a better key. Before:

mysql> explain SELECT * FROM `drydock_lease` WHERE (status IN ('pending', 'acquired', 'active')) ORDER BY `id` DESC LIMIT 101;
+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table         | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | drydock_lease | index | NULL          | PRIMARY | 4       | NULL |  101 | Using where |
+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

After:

mysql> explain SELECT * FROM `drydock_lease` WHERE (status IN ('pending', 'acquired', 'active')) ORDER BY `id` DESC LIMIT 101;
+----+-------------+---------------+-------+---------------+------------+---------+------+------+---------------------------------------+
| id | select_type | table         | type  | possible_keys | key        | key_len | ref  | rows | Extra                                 |
+----+-------------+---------------+-------+---------------+------------+---------+------+------+---------------------------------------+
|  1 | SIMPLE      | drydock_lease | range | key_status    | key_status | 130     | NULL |    5 | Using index condition; Using filesort |
+----+-------------+---------------+-------+---------------+------------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)