Open notbakaneko opened 2 weeks ago
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
Doesn't work, needs to be reordered againlimit
increases but passing and getting the cursor for response does not look like a great time...
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)
I think we should try adding the index first and see if it helps. If it didn't then proceed with this pr. @peppy
try it with user_id = 1040328
🤔
| 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)
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.
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 |
+----+-------------+------------------------+------------+--------+-------------------------------------------------------+-----------------------------------------------+---------+--------------------------------+--------+----------+----------------------------------------------+
I'm doing a bit more testing on this at the moment, and it's really depending on how the records are spread out 😐
Duplicates
ends_at
tomultiplayer_rooms_high
and uses it for sort order. The existing issue is ordering onmultiplayer_rooms
while the existence check usesmultiplayer_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.
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)
UPDATE multiplayer_rooms_high h INNER JOIN multiplayer_rooms r ON h.room_id = r.id SET h.ends_at = r.ends_at