Faster multiplayer room participation listing

[notbakaneko]

Duplicates ends_at to multiplayer_rooms_high and uses it for sort order. The existing issue is ordering on multiplayer_rooms while the existence check uses multiplayer_rooms_high can result in too many records being scanned to get enough matching rows. The issue doesn't exist if there's no sort order, or the required rows are all early in the sorting order.

I made the listing a separate scope so other queries that don't have the issue can skip the join mess.

• [ ] add migration 2024_06_11_000001_add_ends_at_to_multiplayer_rooms_high
• [ ] CREATE INDEX participated_rooms ON multiplayer_rooms_high (ends_at DESC, room_id DESC, user_id)
• [ ] fill column UPDATE multiplayer_rooms_high h INNER JOIN multiplayer_rooms r ON h.room_id = r.id SET h.ends_at = r.ends_at

[notbakaneko]

SELECT * FROM `multiplayer_rooms` where `id` in (
	SELECT `room_id` FROM `multiplayer_rooms_high`
	WHERE `user_id` = 475001
	ORDER BY `ends_at` DESC, `room_id` DESC
) and `TYPE` in ('head_to_head', 'team_versus') and `deleted_at` IS NULL
LIMIT 51;

~~seems to perform better than join when limit increases but passing and getting the cursor for response does not look like a great time...~~ Doesn't work, needs to be reordered again

[nanaya]

fwiw adding the user,room index using table dumps from production:

before:

mysql [osu]> SELECT * FROM `multiplayer_rooms` WHERE `TYPE` in ('head_to_head', 'team_versus') and EXISTS (SELECT * FROM `multiplayer_rooms_high` WHERE `multiplayer_rooms`.`id` = `multiplayer_rooms_high`.`room_id` and `user_id` = 475001) and `multiplayer_rooms`.`deleted_at` IS NULL ORDER BY `ends_at` DESC, `id` DESC LIMIT 51;
Empty set (2.03 sec)

mysql [osu]> SELECT * FROM `multiplayer_rooms` WHERE `TYPE` in ('head_to_head', 'team_versus') and EXISTS (SELECT * FROM `multiplayer_rooms_high` WHERE `multiplayer_rooms`.`id` = `multiplayer_rooms_high`.`room_id` and `user_id` = 475001) and `multiplayer_rooms`.`deleted_at` IS NULL ORDER BY `ends_at` DESC, `id` DESC LIMIT 51;
Empty set (2.02 sec)

after:

mysql [osu]> create index user_room on multiplayer_rooms_high(user_id,room_id);
Query OK, 0 rows affected (2.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [osu]> SELECT * FROM `multiplayer_rooms` WHERE `TYPE` in ('head_to_head', 'team_versus') and EXISTS (SELECT * FROM `multiplayer_rooms_high` WHERE `multiplayer_rooms`.`id` = `multiplayer_rooms_high`.`room_id` and `user_id` = 475001) and `multiplayer_rooms`.`deleted_at` IS NULL ORDER BY `ends_at` DESC, `id` DESC LIMIT 51;
Empty set (0.00 sec)

mysql [osu]> SELECT * FROM `multiplayer_rooms` WHERE `TYPE` in ('head_to_head', 'team_versus') and EXISTS (SELECT * FROM `multiplayer_rooms_high` WHERE `multiplayer_rooms`.`id` = `multiplayer_rooms_high`.`room_id` and `user_id` = 475001) and `multiplayer_rooms`.`deleted_at` IS NULL ORDER BY `ends_at` DESC, `id` DESC LIMIT 51;
Empty set (0.00 sec)

[nanaya]

I think we should try adding the index first and see if it helps. If it didn't then proceed with this pr. @peppy

[notbakaneko]

try it with user_id = 1040328 🤔

[nanaya]

|  37020 |  1040328 | smoogipoo's awesome room                                                              |   33908195 | 2020-12-11 11:51:58 | 2020-12-16 13:38:19 |         NULL |                 1 | NULL                      | head_to_head | host_only               |                   0 |         0 | 2020-12-11 11:51:58 | 2020-12-11 11:51:58 | NULL       | normal   |
+--------+----------+---------------------------------------------------------------------------------------+------------+---------------------+---------------------+--------------+-------------------+---------------------------+--------------+-------------------------+---------------------+-----------+---------------------+---------------------+------------+----------+
44 rows in set (0.01 sec)

[peppy]

I've added the index. I think it will work short term, but if a user ends up participating in 1,000+ multiplayer rooms it will start to falter:

> explain SELECT * FROM `multiplayer_rooms` WHERE `TYPE` in ('head_to_head', 'team_versus') and EXISTS (SELECT * FROM `multiplayer_rooms_high`
                        -> WHERE `multiplayer_rooms`.`id` = `multiplayer_rooms_high`.`room_id` and `user_id` = 1040328) and `multiplayer_rooms`.`deleted_at` IS NULL ORD
                        -> ER BY `ends_at` DESC, `id` DESC LIMIT 51\G;
***************************[ 1. row ]***************************
id            | 1
select_type   | SIMPLE
table         | multiplayer_rooms_high
partitions    | <null>
type          | ref
possible_keys | multiplayer_rooms_high_room_id_user_id_unique,user_room
key           | user_room
key_len       | 4
ref           | const
rows          | 213
filtered      | 100.0
Extra         | Using index; Using temporary; Using filesort
***************************[ 2. row ]***************************
id            | 1
select_type   | SIMPLE
table         | multiplayer_rooms
partitions    | <null>
type          | eq_ref
possible_keys | PRIMARY,multiplayer_rooms_type_category_ends_at_index
key           | PRIMARY
key_len       | 8
ref           | osu.multiplayer_rooms_high.room_id
rows          | 1
filtered      |   5.63
Extra         | Using where

The filesort is the issue here.

[nanaya]

is this still needed? also is this result correct?

mysql [osu]> SELECT multiplayer_rooms.*, multiplayer_rooms_high.room_id FROM `multiplayer_rooms` inner join `multiplayer_rooms_high` on `multiplayer_rooms_high`.`room_id` = `multiplayer_rooms`.`id` WHERE `type` in ('head_to_head','team_versus') and `multiplayer_rooms_high`.`user_id` = 1040328 and `multiplayer_rooms`.`deleted_at` IS NULL ORDER BY `multiplayer_rooms_high`.`ends_at` DESC, `multiplayer_rooms_high`.`room_id` DESC LIMIT 51;
...
|  37020 |  1040328 | smoogipoo's awesome room                                                              |   33908195 | 2020-12-11 11:51:58 | 2020-12-16 13:38:19 |         NULL |                 1 | NULL                      | head_to_head | host_only               |                   0 |         0 | 2020-12-11 11:51:58 | 2020-12-11 11:51:58 | NULL       | normal   |   37020 |
+--------+----------+---------------------------------------------------------------------------------------+------------+---------------------+---------------------+--------------+-------------------+---------------------------+--------------+-------------------------+---------------------+-----------+---------------------+---------------------+------------+----------+---------+
44 rows in set (1.34 sec)

(explain)
+----+-------------+------------------------+------------+--------+-------------------------------------------------------+-----------------------------------------------+---------+--------------------------------+--------+----------+----------------------------------------------+
| id | select_type | table                  | partitions | type   | possible_keys                                         | key                                           | key_len | ref                            | rows   | filtered | Extra                                        |
+----+-------------+------------------------+------------+--------+-------------------------------------------------------+-----------------------------------------------+---------+--------------------------------+--------+----------+----------------------------------------------+
|  1 | SIMPLE      | multiplayer_rooms      | NULL       | ALL    | PRIMARY,multiplayer_rooms_type_category_ends_at_index | NULL                                          | NULL    | NULL                           | 668510 |     5.66 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | multiplayer_rooms_high | NULL       | eq_ref | multiplayer_rooms_high_room_id_user_id_unique         | multiplayer_rooms_high_room_id_user_id_unique | 12      | osu.multiplayer_rooms.id,const |      1 |   100.00 | NULL                                         |
+----+-------------+------------------------+------------+--------+-------------------------------------------------------+-----------------------------------------------+---------+--------------------------------+--------+----------+----------------------------------------------+

[notbakaneko]

I'm doing a bit more testing on this at the moment, and it's really depending on how the records are spread out 😐