search

ppy / osu-web

the browser-facing portion of osu!
https://osu.ppy.sh
GNU Affero General Public License v3.0
981 stars 384 forks source link

[Proposal] A GraphQL API implementation #7351

Open LavaDesu opened 3 years ago

LavaDesu commented 3 years ago

Endpoints like /users/{user} and /beatmapsets/{beatmapset} return huge amounts of data that most clients probably never need.

For instance, beatmapsets.recent_favourites can take up >50% of the total json response, but there are very rare cases where you actually need that as an API consumer.

A GraphQL API can alleviate issues like this on both the client and server by transferring and processing only the necessary data, while also allowing complex yet efficient queries that would otherwise require multiple REST requests.

LavaDesu commented 3 years ago

If the devs are interested, I could try my hand at implementing this. I made a proof of concept on my fork which allows a simple query of a user by id.

peppy commented 3 years ago

@LavaDesu thanks for the proof of concept! are you able to do some benchmarks (just using ab) against a normal API request when only requesting a small subset of data? I'd be interested to know how much performance improvement this provides, as that would be the main reason we would consider taking this on.

LavaDesu commented 3 years ago

I would like to say that the benefits of implementing a GraphQL API is not only performance, but also the flexibility that comes with it. Theoretically, you would be able to do something like

and all that can be done in one single request, which would be faster than multiple REST requests due to overhead. It is also strongly-typed in an easy-to-read schema, and can throw errors if the server sends unconforming data (such as sending null for a non-nullable field).

But here are the "benchmarks", unfortunately there were a few problems with it:

A full GraphQL query of (mostly) everything in the poc

Response time: ~315 ms (avg over 5 requests)

Query ```gql query { user(id: 9) { id username account_history { period reason supporting_url timestamp } achievements { achievement { id description } } active_tournament_banner { id image } avatar_url badges { awarded_at description image_url url } beatmap_playcounts(first: 5) { data { beatmap { id version } playcount } } comments_count country { acronym name } cover { id custom_url url } default_group { id identifier name short_name description colour playmodes is_probationary } discord favourite_beatmapsets(first: 5) { data { beatmapset { id beatmaps { id version } } dateadded } } follower_count groups { id identifier name short_name description colour playmodes is_probationary } has_supported interests is_active is_bot is_deleted is_online is_supporter join_date kudosu { total available } last_visit location mapping_follower_count max_blocks max_friends monthly_playcounts(first: 12) { data { start_date count year_month } } occupation page { raw html } playmode playstyle pm_friends_only post_count previous_usernames profile_colour profile_order rank_history { data mode } replays_watched_counts(first: 12) { data { start_date count year_month } } statistics { country_rank global_rank level { current progress } pp ranked_score hit_accuracy play_count play_time total_score total_gits max_combo replays_watched_by_others is_ranked grade_counts { ss ssh s sh a } } support_level title title_url twitter website } } ```
Query minified into JSON (1325 bytes) ```json {"query":"query{user(id:9){id username account_history{period reason supporting_url timestamp}achievements{achievement{id description}}active_tournament_banner{id image}avatar_url badges{awarded_at description image_url url}beatmap_playcounts(first:5){data{beatmap{id version}playcount}}comments_count country{acronym name}cover{id custom_url url}default_group{id identifier name short_name description colour playmodes is_probationary}discord favourite_beatmapsets(first: 5){data{beatmapset{id beatmaps{id version}}dateadded}}follower_count groups{id identifier name short_name description colour playmodes is_probationary}has_supported interests is_active is_bot is_deleted is_online is_supporter join_date kudosu{total available}last_visit location mapping_follower_count max_blocks max_friends monthly_playcounts(first:12){data{start_date count year_month}}occupation page{raw html}playmode playstyle pm_friends_only post_count previous_usernames profile_colour profile_order rank_history{data mode}replays_watched_counts(first:12){data{start_date count year_month}}statistics{country_rank global_rank level{current progress}pp ranked_score hit_accuracy play_count play_time total_score total_gits max_combo replays_watched_by_others is_ranked grade_counts{ss ssh s sh a}}support_level title title_url twitter website}}"} ```
Response (4439 bytes) ```json {"data":{"user":{"id":"9","username":"johan gibson","account_history":[{"period":38400,"reason":"grow collaborative niches","supporting_url":null,"timestamp":"2021-03-23T02:36:16+00:00"},{"period":600,"reason":"brand extensible experiences","supporting_url":null,"timestamp":"2021-03-23T02:36:16+00:00"}],"achievements":[],"active_tournament_banner":null,"avatar_url":"\/images\/layout\/avatar-guest.png","badges":[],"beatmap_playcounts":{"data":[{"beatmap":{"id":"816636","version":"Treasure"},"playcount":1486},{"beatmap":{"id":"816636","version":"Treasure"},"playcount":1472},{"beatmap":{"id":"816636","version":"Treasure"},"playcount":1464},{"beatmap":{"id":"816636","version":"Treasure"},"playcount":1331},{"beatmap":{"id":"816636","version":"Treasure"},"playcount":1207}]},"comments_count":0,"country":{"acronym":"AU","name":"Australia"},"cover":{"id":null,"custom_url":null,"url":null},"default_group":{"id":"6","identifier":"default","name":"default","short_name":"default","description":"default","colour":null,"playmodes":null,"is_probationary":false},"discord":null,"favourite_beatmapsets":{"data":[{"beatmapset":{"id":"320717","beatmaps":[{"id":"713594","version":"Eternal Happiness"},{"id":"862994","version":"A Mystery's Insane"},{"id":"863922","version":"Karia's Hard"},{"id":"865638","version":"Gaia's Normal"},{"id":"865639","version":"Easy"}]},"dateadded":"2021-03-23T02:38:25+00:00"},{"beatmapset":{"id":"320717","beatmaps":[{"id":"713594","version":"Eternal Happiness"},{"id":"862994","version":"A Mystery's Insane"},{"id":"863922","version":"Karia's Hard"},{"id":"865638","version":"Gaia's Normal"},{"id":"865639","version":"Easy"}]},"dateadded":"2021-03-23T02:38:25+00:00"},{"beatmapset":{"id":"320717","beatmaps":[{"id":"713594","version":"Eternal Happiness"},{"id":"862994","version":"A Mystery's Insane"},{"id":"863922","version":"Karia's Hard"},{"id":"865638","version":"Gaia's Normal"},{"id":"865639","version":"Easy"}]},"dateadded":"2021-03-23T02:38:25+00:00"},{"beatmapset":{"id":"320717","beatmaps":[{"id":"713594","version":"Eternal Happiness"},{"id":"862994","version":"A Mystery's Insane"},{"id":"863922","version":"Karia's Hard"},{"id":"865638","version":"Gaia's Normal"},{"id":"865639","version":"Easy"}]},"dateadded":"2021-03-23T02:38:25+00:00"},{"beatmapset":{"id":"320717","beatmaps":[{"id":"713594","version":"Eternal Happiness"},{"id":"862994","version":"A Mystery's Insane"},{"id":"863922","version":"Karia's Hard"},{"id":"865638","version":"Gaia's Normal"},{"id":"865639","version":"Easy"}]},"dateadded":"2021-03-23T02:38:25+00:00"}]},"follower_count":0,"groups":[],"has_supported":false,"interests":"mas","is_active":true,"is_bot":false,"is_deleted":false,"is_online":false,"is_supporter":true,"join_date":"2019-06-03T01:38:09+00:00","kudosu":{"total":197,"available":401},"last_visit":"2021-03-23T02:36:14+00:00","location":null,"mapping_follower_count":0,"max_blocks":50,"max_friends":500,"monthly_playcounts":{"data":[]},"occupation":"ion","page":{"raw":"","html":""},"playmode":"osu","playstyle":["touch"],"pm_friends_only":false,"post_count":233,"previous_usernames":[],"profile_colour":null,"profile_order":["me","recent_activity","top_ranks","medals","historical","beatmaps","kudosu"],"rank_history":{"data":[2107036,2109145,2113372,2115487,2117605,1925095,1927022,1751838,1755349,1757106,1760627,1600570,1602172,1456520,1457978,1459437,1462362,1282774,1125240,1126366,1127493,1129753,1107601,1108710,1109820,1110931,1089148,1090238,1092423,1093517,1094612,1095708,1096805,997095,998093,1000093,1001094,910085,910996,911908,913735,915566,917401,804738,805544,807158,808776,792918,793712,795303,796897,798494,700433,636757,637394,638032,638671,639951,627403,628660,629920,630551,631815,574377,575528,564243,564808,565940,496439,497434,436346,437220,437658,438096,438535,438974,399067,399466,400267,392419,392812,393599,393993,358175,358534,325940,326593,286485,286772,287347],"mode":"osu"},"replays_watched_counts":{"data":[]},"statistics":{"country_rank":1,"global_rank":287347,"level":{"current":44,"progress":0},"pp":694,"ranked_score":817025960,"hit_accuracy":97.028,"play_count":188981,"play_time":9624838,"total_score":1143836344,"total_gits":1963043,"max_combo":2271,"replays_watched_by_others":0,"is_ranked":true,"grade_counts":{"ss":189,"ssh":57,"s":9449,"sh":3780,"a":37796}},"support_level":1,"title":null,"title_url":null,"twitter":"ppy","website":"http:\/\/www.google.com\/"}}} ```
Database query ``` 210326 18:49:35 126 Query use `osu` 126 Prepare set names 'utf8mb4' collate 'utf8mb4_unicode_ci' 126 Execute set names 'utf8mb4' collate 'utf8mb4_unicode_ci' 126 Close stmt 126 Prepare set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' 126 Execute set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' 126 Close stmt 126 Prepare select * from `oauth_access_tokens` where `id` = ? limit 1 126 Execute select * from `oauth_access_tokens` where `id` = '592a53a34fcdce834e610adedb3f771dda6d584a6e31681456e1cd74bc52eb8285199cd289cc804d' limit 1 126 Close stmt 126 Prepare select * from `oauth_access_tokens` where `id` = ? limit 1 126 Execute select * from `oauth_access_tokens` where `id` = '592a53a34fcdce834e610adedb3f771dda6d584a6e31681456e1cd74bc52eb8285199cd289cc804d' limit 1 126 Close stmt 126 Prepare select * from `oauth_clients` where `id` = ? limit 1 126 Execute select * from `oauth_clients` where `id` = '1' limit 1 126 Close stmt 126 Prepare select * from `phpbb_users` where `user_id` is null limit 1 126 Execute select * from `phpbb_users` where `user_id` is null limit 1 126 Close stmt 126 Prepare select * from `oauth_access_tokens` where `id` = ? limit 1 126 Execute select * from `oauth_access_tokens` where `id` = '592a53a34fcdce834e610adedb3f771dda6d584a6e31681456e1cd74bc52eb8285199cd289cc804d' limit 1 126 Close stmt 126 Prepare select * from `oauth_access_tokens` where `id` = ? limit 1 126 Execute select * from `oauth_access_tokens` where `id` = '592a53a34fcdce834e610adedb3f771dda6d584a6e31681456e1cd74bc52eb8285199cd289cc804d' limit 1 126 Close stmt 126 Prepare select * from `oauth_clients` where `id` = ? limit 1 126 Execute select * from `oauth_clients` where `id` = '1' limit 1 126 Close stmt 126 Prepare select * from `phpbb_users` where `user_id` is null limit 1 126 Execute select * from `phpbb_users` where `user_id` is null limit 1 126 Close stmt 126 Prepare select * from `phpbb_users` where `user_id` = ? limit 1 126 Execute select * from `phpbb_users` where `user_id` = '9' limit 1 126 Close stmt 126 Prepare select * from `osu_user_achievements` where `osu_user_achievements`.`user_id` = ? and `osu_user_achievements`.`user_id` is not null order by `date` desc 126 Execute select * from `osu_user_achievements` where `osu_user_achievements`.`user_id` = 9 and `osu_user_achievements`.`user_id` is not null order by `date` desc 126 Close stmt 126 Prepare select * from `osu_profile_banners` where `osu_profile_banners`.`user_id` = ? and `osu_profile_banners`.`user_id` is not null order by `banner_id` desc limit 1 126 Execute select * from `osu_profile_banners` where `osu_profile_banners`.`user_id` = 9 and `osu_profile_banners`.`user_id` is not null order by `banner_id` desc limit 1 126 Close stmt 126 Prepare select * from `osu_badges` where `osu_badges`.`user_id` = ? and `osu_badges`.`user_id` is not null order by `awarded` desc 126 Execute select * from `osu_badges` where `osu_badges`.`user_id` = 9 and `osu_badges`.`user_id` is not null order by `awarded` desc 126 Close stmt 126 Prepare select count(*) as aggregate from `comments` where `comments`.`user_id` = ? and `comments`.`user_id` is not null and `deleted_at` is null 126 Execute select count(*) as aggregate from `comments` where `comments`.`user_id` = 9 and `comments`.`user_id` is not null and `deleted_at` is null 126 Close stmt 126 Prepare select * from `user_profile_customizations` where `user_profile_customizations`.`user_id` = ? and `user_profile_customizations`.`user_id` is not null limit 1 126 Execute select * from `user_profile_customizations` where `user_profile_customizations`.`user_id` = 9 and `user_profile_customizations`.`user_id` is not null limit 1 126 Close stmt 126 Prepare select * from `phpbb_user_group` where `phpbb_user_group`.`user_id` = ? and `phpbb_user_group`.`user_id` is not null 126 Execute select * from `phpbb_user_group` where `phpbb_user_group`.`user_id` = 9 and `phpbb_user_group`.`user_id` is not null 126 Close stmt 126 Prepare select `user_id`, `username_last` from `osu_username_change_history` where `osu_username_change_history`.`user_id` = ? and `osu_username_change_history`.`user_id` is not null and `type` in (?, ?, ?) and `username_last` is not null and `username_last` <> ? order by `timestamp` asc 126 Execute select `user_id`, `username_last` from `osu_username_change_history` where `osu_username_change_history`.`user_id` = 9 and `osu_username_change_history`.`user_id` is not null and `type` in ('support', 'paid', 'admin') and `username_last` is not null and `username_last` <> '' order by `timestamp` asc 126 Close stmt 126 Prepare select * from `osu_user_performance_rank` where `osu_user_performance_rank`.`user_id` = ? and `osu_user_performance_rank`.`user_id` is not null and `mode` = ? limit 1 126 Execute select * from `osu_user_performance_rank` where `osu_user_performance_rank`.`user_id` = 9 and `osu_user_performance_rank`.`user_id` is not null and `mode` = 0 limit 1 126 Close stmt 126 Prepare select * from `osu_counts` where `osu_counts`.`name` = ? limit 1 126 Execute select * from `osu_counts` where `osu_counts`.`name` = 'pp_rank_column' limit 1 126 Close stmt 126 Prepare select * from `phpbb_users` where `phpbb_users`.`user_id` = ? limit 1 126 Execute select * from `phpbb_users` where `phpbb_users`.`user_id` = 9 limit 1 126 Close stmt 126 Prepare select * from `osu_user_stats` where `osu_user_stats`.`user_id` = ? and `osu_user_stats`.`user_id` is not null limit 1 126 Execute select * from `osu_user_stats` where `osu_user_stats`.`user_id` = 9 and `osu_user_stats`.`user_id` is not null limit 1 126 Close stmt 126 Prepare select * from `osu_counts` where `osu_counts`.`name` = ? limit 1 126 Execute select * from `osu_counts` where `osu_counts`.`name` = 'pp_rank_column' limit 1 126 Close stmt 126 Prepare select * from `osu_user_stats` where `osu_user_stats`.`user_id` = ? and `osu_user_stats`.`user_id` is not null limit 1 126 Execute select * from `osu_user_stats` where `osu_user_stats`.`user_id` = 9 and `osu_user_stats`.`user_id` is not null limit 1 126 Close stmt 126 Prepare select count(*) as aggregate from `osu_user_stats` where `country_acronym` = ? and `rank_score` > (select `rank_score` from `osu_user_stats` where `user_id` = ?) 126 Execute select count(*) as aggregate from `osu_user_stats` where `country_acronym` = 'GB' and `rank_score` > (select `rank_score` from `osu_user_stats` where `user_id` = 9) 126 Close stmt 126 Prepare select * from `osu_user_donations` where `osu_user_donations`.`user_id` = ? and `osu_user_donations`.`user_id` is not null 126 Execute select * from `osu_user_donations` where `osu_user_donations`.`user_id` = 9 and `osu_user_donations`.`user_id` is not null 126 Close stmt 126 Prepare select * from `phpbb_ranks` where `phpbb_ranks`.`rank_id` = ? limit 1 126 Execute select * from `phpbb_ranks` where `phpbb_ranks`.`rank_id` = 0 limit 1 126 Close stmt 126 Prepare select * from `osu_user_banhistory` where `osu_user_banhistory`.`user_id` in (9) 126 Execute select * from `osu_user_banhistory` where `osu_user_banhistory`.`user_id` in (9) 126 Close stmt 126 Prepare select `user_id`, (select count(*) from `osu_user_beatmap_playcount` where `phpbb_users`.`user_id` = `osu_user_beatmap_playcount`.`user_id`) as `beatmap_playcounts_count` from `phpbb_users` where `phpbb_users`.`user_id` in (?) 126 Execute select `user_id`, (select count(*) from `osu_user_beatmap_playcount` where `phpbb_users`.`user_id` = `osu_user_beatmap_playcount`.`user_id`) as `beatmap_playcounts_count` from `phpbb_users` where `phpbb_users`.`user_id` in (9) 126 Close stmt 126 Prepare select * from `osu_user_beatmap_playcount` where `osu_user_beatmap_playcount`.`user_id` in (9) order by `playcount` desc limit 5 offset 0 126 Execute select * from `osu_user_beatmap_playcount` where `osu_user_beatmap_playcount`.`user_id` in (9) order by `playcount` desc limit 5 offset 0 126 Close stmt 126 Prepare select * from `osu_countries` where `osu_countries`.`acronym` in (?) 126 Execute select * from `osu_countries` where `osu_countries`.`acronym` in ('AU') 126 Close stmt 126 Prepare select `user_id`, (select count(*) from `osu_favouritemaps` where `phpbb_users`.`user_id` = `osu_favouritemaps`.`user_id`) as `favourites_count` from `phpbb_users` where `phpbb_users`.`user_id` in (?) 126 Execute select `user_id`, (select count(*) from `osu_favouritemaps` where `phpbb_users`.`user_id` = `osu_favouritemaps`.`user_id`) as `favourites_count` from `phpbb_users` where `phpbb_users`.`user_id` in (9) 126 Close stmt 126 Prepare select * from `osu_favouritemaps` where `osu_favouritemaps`.`user_id` in (9) order by `dateadded` desc limit 5 offset 0 126 Execute select * from `osu_favouritemaps` where `osu_favouritemaps`.`user_id` in (9) order by `dateadded` desc limit 5 offset 0 126 Close stmt 126 Prepare select `user_id`, (select count(*) from `osu_user_month_playcount` where `phpbb_users`.`user_id` = `osu_user_month_playcount`.`user_id`) as `monthly_playcounts_count` from `phpbb_users` where `phpbb_users`.`user_id` in (?) 126 Execute select `user_id`, (select count(*) from `osu_user_month_playcount` where `phpbb_users`.`user_id` = `osu_user_month_playcount`.`user_id`) as `monthly_playcounts_count` from `phpbb_users` where `phpbb_users`.`user_id` in (9) 126 Close stmt 126 Prepare select * from `osu_user_month_playcount` where `osu_user_month_playcount`.`user_id` in (9) limit 12 offset 0 126 Execute select * from `osu_user_month_playcount` where `osu_user_month_playcount`.`user_id` in (9) limit 12 offset 0 126 Close stmt 126 Prepare select `user_id`, (select count(*) from `osu_user_replayswatched` where `phpbb_users`.`user_id` = `osu_user_replayswatched`.`user_id`) as `replays_watched_counts_count` from `phpbb_users` where `phpbb_users`.`user_id` in (?) 126 Execute select `user_id`, (select count(*) from `osu_user_replayswatched` where `phpbb_users`.`user_id` = `osu_user_replayswatched`.`user_id`) as `replays_watched_counts_count` from `phpbb_users` where `phpbb_users`.`user_id` in (9) 126 Close stmt 126 Prepare select * from `osu_user_replayswatched` where `osu_user_replayswatched`.`user_id` in (9) limit 12 offset 0 126 Execute select * from `osu_user_replayswatched` where `osu_user_replayswatched`.`user_id` in (9) limit 12 offset 0 126 Close stmt 126 Prepare select * from `osu_beatmaps` where `osu_beatmaps`.`beatmap_id` in (816636) and `osu_beatmaps`.`deleted_at` is null 126 Execute select * from `osu_beatmaps` where `osu_beatmaps`.`beatmap_id` in (816636) and `osu_beatmaps`.`deleted_at` is null 126 Close stmt 126 Prepare select * from `osu_beatmapsets` where `osu_beatmapsets`.`beatmapset_id` in (320717) and `osu_beatmapsets`.`deleted_at` is null and `active` = ? 126 Execute select * from `osu_beatmapsets` where `osu_beatmapsets`.`beatmapset_id` in (320717) and `osu_beatmapsets`.`deleted_at` is null and `active` = 1 126 Close stmt 126 Prepare select * from `osu_beatmaps` where `osu_beatmaps`.`beatmapset_id` in (320717) and `osu_beatmaps`.`deleted_at` is null 126 Execute select * from `osu_beatmaps` where `osu_beatmaps`.`beatmapset_id` in (320717) and `osu_beatmaps`.`deleted_at` is null 126 Close stmt ```

A "more realistic" GraphQL query, in this case it could be a stat tracker requesting relevant data

Response time: ~256ms (avg over 5 requests)

Query ```gql query { user(id: 9) { id username avatar_url country { acronym name } is_online is_supporter monthly_playcounts(first: 12) { data { start_date count year_month } } playmode playstyle rank_history { data mode } statistics { country_rank global_rank level { current progress } pp ranked_score hit_accuracy play_count play_time total_score total_gits max_combo replays_watched_by_others grade_counts { ss ssh s sh a } } } } ```
Query minified into JSON (394 bytes) ```json {"query":"{user(id:9){id username avatar_url country{acronym name}is_online is_supporter monthly_playcounts(first:12){data{start_date count year_month}}playmode playstyle rank_history{data mode}statistics{country_rank global_rank level{current progress}pp ranked_score hit_accuracy play_count play_time total_score total_gits max_combo replays_watched_by_others grade_counts{ss ssh s sh a}}}}"} ```
Response (1306 bytes) ```json {"data":{"user":{"id":"9","username":"johan gibson","avatar_url":"\/images\/layout\/avatar-guest.png","country":{"acronym":"AU","name":"Australia"},"is_online":false,"is_supporter":true,"monthly_playcounts":{"data":[]},"playmode":"osu","playstyle":["touch"],"rank_history":{"data":[2107036,2109145,2113372,2115487,2117605,1925095,1927022,1751838,1755349,1757106,1760627,1600570,1602172,1456520,1457978,1459437,1462362,1282774,1125240,1126366,1127493,1129753,1107601,1108710,1109820,1110931,1089148,1090238,1092423,1093517,1094612,1095708,1096805,997095,998093,1000093,1001094,910085,910996,911908,913735,915566,917401,804738,805544,807158,808776,792918,793712,795303,796897,798494,700433,636757,637394,638032,638671,639951,627403,628660,629920,630551,631815,574377,575528,564243,564808,565940,496439,497434,436346,437220,437658,438096,438535,438974,399067,399466,400267,392419,392812,393599,393993,358175,358534,325940,326593,286485,286772,287347],"mode":"osu"},"statistics":{"country_rank":1,"global_rank":287347,"level":{"current":44,"progress":0},"pp":694,"ranked_score":817025960,"hit_accuracy":97.028,"play_count":188981,"play_time":9624838,"total_score":1143836344,"total_gits":1963043,"max_combo":2271,"replays_watched_by_others":0,"grade_counts":{"ss":189,"ssh":57,"s":9449,"sh":3780,"a":37796}}}}} ```
Database query ``` 210326 18:53:39 125 Query use `osu` 125 Prepare set names 'utf8mb4' collate 'utf8mb4_unicode_ci' 125 Execute set names 'utf8mb4' collate 'utf8mb4_unicode_ci' 125 Close stmt 125 Prepare set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' 125 Execute set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' 125 Close stmt 125 Prepare select * from `oauth_access_tokens` where `id` = ? limit 1 125 Execute select * from `oauth_access_tokens` where `id` = '592a53a34fcdce834e610adedb3f771dda6d584a6e31681456e1cd74bc52eb8285199cd289cc804d' limit 1 125 Close stmt 125 Prepare select * from `oauth_access_tokens` where `id` = ? limit 1 125 Execute select * from `oauth_access_tokens` where `id` = '592a53a34fcdce834e610adedb3f771dda6d584a6e31681456e1cd74bc52eb8285199cd289cc804d' limit 1 125 Close stmt 125 Prepare select * from `oauth_clients` where `id` = ? limit 1 125 Execute select * from `oauth_clients` where `id` = '1' limit 1 125 Close stmt 125 Prepare select * from `phpbb_users` where `user_id` is null limit 1 125 Execute select * from `phpbb_users` where `user_id` is null limit 1 125 Close stmt 125 Prepare select * from `oauth_access_tokens` where `id` = ? limit 1 125 Execute select * from `oauth_access_tokens` where `id` = '592a53a34fcdce834e610adedb3f771dda6d584a6e31681456e1cd74bc52eb8285199cd289cc804d' limit 1 125 Close stmt 125 Prepare select * from `oauth_access_tokens` where `id` = ? limit 1 125 Execute select * from `oauth_access_tokens` where `id` = '592a53a34fcdce834e610adedb3f771dda6d584a6e31681456e1cd74bc52eb8285199cd289cc804d' limit 1 125 Close stmt 125 Prepare select * from `oauth_clients` where `id` = ? limit 1 125 Execute select * from `oauth_clients` where `id` = '1' limit 1 125 Close stmt 125 Prepare select * from `phpbb_users` where `user_id` is null limit 1 125 Execute select * from `phpbb_users` where `user_id` is null limit 1 125 Close stmt 125 Prepare select * from `phpbb_users` where `user_id` = ? limit 1 125 Execute select * from `phpbb_users` where `user_id` = '9' limit 1 125 Close stmt 125 Prepare select * from `osu_user_performance_rank` where `osu_user_performance_rank`.`user_id` = ? and `osu_user_performance_rank`.`user_id` is not null and `mode` = ? limit 1 125 Execute select * from `osu_user_performance_rank` where `osu_user_performance_rank`.`user_id` = 9 and `osu_user_performance_rank`.`user_id` is not null and `mode` = 0 limit 1 125 Close stmt 125 Prepare select * from `osu_counts` where `osu_counts`.`name` = ? limit 1 125 Execute select * from `osu_counts` where `osu_counts`.`name` = 'pp_rank_column' limit 1 125 Close stmt 125 Prepare select * from `phpbb_users` where `phpbb_users`.`user_id` = ? limit 1 125 Execute select * from `phpbb_users` where `phpbb_users`.`user_id` = 9 limit 1 125 Close stmt 125 Prepare select * from `osu_user_stats` where `osu_user_stats`.`user_id` = ? and `osu_user_stats`.`user_id` is not null limit 1 125 Execute select * from `osu_user_stats` where `osu_user_stats`.`user_id` = 9 and `osu_user_stats`.`user_id` is not null limit 1 125 Close stmt 125 Prepare select * from `osu_counts` where `osu_counts`.`name` = ? limit 1 125 Execute select * from `osu_counts` where `osu_counts`.`name` = 'pp_rank_column' limit 1 125 Close stmt 125 Prepare select * from `osu_user_stats` where `osu_user_stats`.`user_id` = ? and `osu_user_stats`.`user_id` is not null limit 1 125 Execute select * from `osu_user_stats` where `osu_user_stats`.`user_id` = 9 and `osu_user_stats`.`user_id` is not null limit 1 125 Close stmt 125 Prepare select count(*) as aggregate from `osu_user_stats` where `country_acronym` = ? and `rank_score` > (select `rank_score` from `osu_user_stats` where `user_id` = ?) 125 Execute select count(*) as aggregate from `osu_user_stats` where `country_acronym` = 'GB' and `rank_score` > (select `rank_score` from `osu_user_stats` where `user_id` = 9) 125 Close stmt 125 Prepare select * from `osu_countries` where `osu_countries`.`acronym` in (?) 125 Execute select * from `osu_countries` where `osu_countries`.`acronym` in ('AU') 125 Close stmt 125 Prepare select `user_id`, (select count(*) from `osu_user_month_playcount` where `phpbb_users`.`user_id` = `osu_user_month_playcount`.`user_id`) as `monthly_playcounts_count` from `phpbb_users` where `phpbb_users`.`user_id` in (?) 125 Execute select `user_id`, (select count(*) from `osu_user_month_playcount` where `phpbb_users`.`user_id` = `osu_user_month_playcount`.`user_id`) as `monthly_playcounts_count` from `phpbb_users` where `phpbb_users`.`user_id` in (9) 125 Close stmt 125 Prepare select * from `osu_user_month_playcount` where `osu_user_month_playcount`.`user_id` in (9) limit 12 offset 0 125 Execute select * from `osu_user_month_playcount` where `osu_user_month_playcount`.`user_id` in (9) limit 12 offset 0 125 Close stmt ```

A super small query requesting just a username from an id

Response time: ~228 ms (avg over 5 requests)

Query ```gql query { user(id: 9) { username } } ```
Query minimised into JSON (34 bytes) ```json {"query":"{user(id:9){username}}"} ```
Response (45 bytes) ```json {"data":{"user":{"username":"johan gibson"}}} ```
Database query ``` 210326 20:22:01 126 Query use `osu` 126 Prepare set names 'utf8mb4' collate 'utf8mb4_unicode_ci' 126 Execute set names 'utf8mb4' collate 'utf8mb4_unicode_ci' 126 Close stmt 126 Prepare set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' 126 Execute set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' 126 Close stmt 126 Prepare select * from `oauth_access_tokens` where `id` = ? limit 1 126 Execute select * from `oauth_access_tokens` where `id` = '592a53a34fcdce834e610adedb3f771dda6d584a6e31681456e1cd74bc52eb8285199cd289cc804d' limit 1 126 Close stmt 126 Prepare select * from `oauth_access_tokens` where `id` = ? limit 1 126 Execute select * from `oauth_access_tokens` where `id` = '592a53a34fcdce834e610adedb3f771dda6d584a6e31681456e1cd74bc52eb8285199cd289cc804d' limit 1 126 Close stmt 126 Prepare select * from `oauth_clients` where `id` = ? limit 1 126 Execute select * from `oauth_clients` where `id` = '1' limit 1 126 Close stmt 126 Prepare select * from `phpbb_users` where `user_id` is null limit 1 126 Execute select * from `phpbb_users` where `user_id` is null limit 1 126 Close stmt 126 Prepare select * from `oauth_access_tokens` where `id` = ? limit 1 126 Execute select * from `oauth_access_tokens` where `id` = '592a53a34fcdce834e610adedb3f771dda6d584a6e31681456e1cd74bc52eb8285199cd289cc804d' limit 1 126 Close stmt 126 Prepare select * from `oauth_access_tokens` where `id` = ? limit 1 126 Execute select * from `oauth_access_tokens` where `id` = '592a53a34fcdce834e610adedb3f771dda6d584a6e31681456e1cd74bc52eb8285199cd289cc804d' limit 1 126 Close stmt 126 Prepare select * from `oauth_clients` where `id` = ? limit 1 126 Execute select * from `oauth_clients` where `id` = '1' limit 1 126 Close stmt 126 Prepare select * from `phpbb_users` where `user_id` is null limit 1 126 Execute select * from `phpbb_users` where `user_id` is null limit 1 126 Close stmt 126 Prepare select * from `phpbb_users` where `user_id` = ? limit 1 126 Execute select * from `phpbb_users` where `user_id` = '9' limit 1 126 Close stmt ```

A normal /users/{id}/osu POST request

Response time: ~263 ms (avg over 5 requests)

Response (3436 bytes) ```json {"avatar_url":"\/images\/layout\/avatar-guest.png","country_code":"AU","default_group":"default","id":9,"is_active":true,"is_bot":false,"is_deleted":false,"is_online":false,"is_supporter":true,"last_visit":"2021-03-23T02:36:14+00:00","pm_friends_only":false,"profile_colour":null,"username":"johan gibson","comments_count":0,"cover_url":"http:\/\/osu.peach.local\/\/images\/headers\/profile-covers\/c2.jpg","discord":null,"has_supported":false,"interests":"mas","join_date":"2019-06-03T01:38:09+00:00","kudosu":{"total":197,"available":401},"location":null,"max_blocks":50,"max_friends":500,"occupation":"ion","playmode":"osu","playstyle":["touch"],"post_count":233,"profile_order":["me","recent_activity","top_ranks","medals","historical","beatmaps","kudosu"],"title":null,"title_url":null,"twitter":"ppy","website":"http:\/\/www.google.com\/","country":{"code":"AU","name":"Australia"},"cover":{"custom_url":null,"url":"http:\/\/osu.peach.local\/\/images\/headers\/profile-covers\/c2.jpg","id":"2"},"account_history":[{"description":"grow collaborative niches","type":"silence","timestamp":"2021-03-23T02:36:16+00:00","length":38400}],"active_tournament_banner":null,"badges":[],"beatmap_playcounts_count":13,"favourite_beatmapset_count":20,"follower_count":0,"graveyard_beatmapset_count":0,"groups":[],"loved_beatmapset_count":0,"mapping_follower_count":0,"monthly_playcounts":[],"page":{"html":"","raw":""},"previous_usernames":[],"ranked_and_approved_beatmapset_count":4,"replays_watched_counts":[],"scores_best_count":0,"scores_first_count":4,"scores_recent_count":20,"statistics":{"level":{"current":44,"progress":0},"global_rank":287347,"pp":694,"ranked_score":817025960,"hit_accuracy":97.028,"play_count":188981,"play_time":9624838,"total_score":1143836344,"total_hits":1963043,"maximum_combo":2271,"replays_watched_by_others":0,"is_ranked":true,"grade_counts":{"ss":189,"ssh":57,"s":9449,"sh":3780,"a":37796},"country_rank":1,"rank":{"country":1}},"support_level":1,"unranked_beatmapset_count":0,"user_achievements":[],"rankHistory":{"mode":"osu","data":[2107036,2109145,2113372,2115487,2117605,1925095,1927022,1751838,1755349,1757106,1760627,1600570,1602172,1456520,1457978,1459437,1462362,1282774,1125240,1126366,1127493,1129753,1107601,1108710,1109820,1110931,1089148,1090238,1092423,1093517,1094612,1095708,1096805,997095,998093,1000093,1001094,910085,910996,911908,913735,915566,917401,804738,805544,807158,808776,792918,793712,795303,796897,798494,700433,636757,637394,638032,638671,639951,627403,628660,629920,630551,631815,574377,575528,564243,564808,565940,496439,497434,436346,437220,437658,438096,438535,438974,399067,399466,400267,392419,392812,393599,393993,358175,358534,325940,326593,286485,286772,287347]},"rank_history":{"mode":"osu","data":[2107036,2109145,2113372,2115487,2117605,1925095,1927022,1751838,1755349,1757106,1760627,1600570,1602172,1456520,1457978,1459437,1462362,1282774,1125240,1126366,1127493,1129753,1107601,1108710,1109820,1110931,1089148,1090238,1092423,1093517,1094612,1095708,1096805,997095,998093,1000093,1001094,910085,910996,911908,913735,915566,917401,804738,805544,807158,808776,792918,793712,795303,796897,798494,700433,636757,637394,638032,638671,639951,627403,628660,629920,630551,631815,574377,575528,564243,564808,565940,496439,497434,436346,437220,437658,438096,438535,438974,399067,399466,400267,392419,392812,393599,393993,358175,358534,325940,326593,286485,286772,287347]}} ```
Database query ``` 210326 18:56:08 124 Query use `osu` 124 Prepare set names 'utf8mb4' collate 'utf8mb4_unicode_ci' 124 Execute set names 'utf8mb4' collate 'utf8mb4_unicode_ci' 124 Close stmt 124 Prepare set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' 124 Execute set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' 124 Close stmt 124 Prepare select * from `oauth_access_tokens` where `id` = ? limit 1 124 Execute select * from `oauth_access_tokens` where `id` = '592a53a34fcdce834e610adedb3f771dda6d584a6e31681456e1cd74bc52eb8285199cd289cc804d' limit 1 124 Close stmt 124 Prepare select * from `oauth_clients` where `id` = ? limit 1 124 Execute select * from `oauth_clients` where `id` = '1' limit 1 124 Close stmt 124 Prepare select * from `oauth_access_tokens` where `oauth_access_tokens`.`client_id` = ? and `oauth_access_tokens`.`client_id` is not null and `revoked` = ? and `expires_at` > ? and `oauth_access_tokens`.`id` = ? limit 1 124 Execute select * from `oauth_access_tokens` where `oauth_access_tokens`.`client_id` = 1 and `oauth_access_tokens`.`client_id` is not null and `revoked` = 0 and `expires_at` > '2021-03-26 11:56:08' and `oauth_access_tokens`.`id` = '592a53a34fcdce834e610adedb3f771dda6d584a6e31681456e1cd74bc52eb8285199cd289cc804d' limit 1 124 Close stmt 124 Prepare select * from `oauth_clients` where `oauth_clients`.`id` = ? limit 1 124 Execute select * from `oauth_clients` where `oauth_clients`.`id` = 1 limit 1 124 Close stmt 124 Prepare select * from `phpbb_users` where `user_id` = ? limit 1 124 Execute select * from `phpbb_users` where `user_id` = '9' limit 1 124 Close stmt 124 Prepare select * from `user_profile_customizations` where `user_profile_customizations`.`user_id` = ? and `user_profile_customizations`.`user_id` is not null limit 1 124 Execute select * from `user_profile_customizations` where `user_profile_customizations`.`user_id` = 9 and `user_profile_customizations`.`user_id` is not null limit 1 124 Close stmt 124 Prepare select count(*) as aggregate from `comments` where `comments`.`user_id` = ? and `comments`.`user_id` is not null and `deleted_at` is null 124 Execute select count(*) as aggregate from `comments` where `comments`.`user_id` = 9 and `comments`.`user_id` is not null and `deleted_at` is null 124 Close stmt 124 Prepare select * from `phpbb_ranks` where `phpbb_ranks`.`rank_id` = ? limit 1 124 Execute select * from `phpbb_ranks` where `phpbb_ranks`.`rank_id` = 0 limit 1 124 Close stmt 124 Prepare select * from `osu_countries` where `osu_countries`.`acronym` = ? limit 1 124 Execute select * from `osu_countries` where `osu_countries`.`acronym` = 'AU' limit 1 124 Close stmt 124 Prepare select * from `osu_user_banhistory` where `osu_user_banhistory`.`user_id` = ? and `osu_user_banhistory`.`user_id` is not null and `timestamp` > ? and `ban_status` = ? order by `timestamp` desc 124 Execute select * from `osu_user_banhistory` where `osu_user_banhistory`.`user_id` = 9 and `osu_user_banhistory`.`user_id` is not null and `timestamp` > '2021-02-26 11:56:08' and `ban_status` = 2 order by `timestamp` desc 124 Close stmt 124 Prepare select * from `osu_profile_banners` where `osu_profile_banners`.`user_id` = ? and `osu_profile_banners`.`user_id` is not null order by `banner_id` desc limit 1 124 Execute select * from `osu_profile_banners` where `osu_profile_banners`.`user_id` = 9 and `osu_profile_banners`.`user_id` is not null order by `banner_id` desc limit 1 124 Close stmt 124 Prepare select * from `osu_badges` where `osu_badges`.`user_id` = ? and `osu_badges`.`user_id` is not null order by `awarded` desc 124 Execute select * from `osu_badges` where `osu_badges`.`user_id` = 9 and `osu_badges`.`user_id` is not null order by `awarded` desc 124 Close stmt 124 Prepare select count(*) as aggregate from `osu_user_beatmap_playcount` where `osu_user_beatmap_playcount`.`user_id` = ? and `osu_user_beatmap_playcount`.`user_id` is not null 124 Execute select count(*) as aggregate from `osu_user_beatmap_playcount` where `osu_user_beatmap_playcount`.`user_id` = 9 and `osu_user_beatmap_playcount`.`user_id` is not null 124 Close stmt 124 Prepare select count(*) as aggregate from `osu_beatmapsets` inner join `osu_favouritemaps` on `osu_favouritemaps`.`beatmapset_id` = `osu_beatmapsets`.`beatmapset_id` where `osu_favouritemaps`.`user_id` = ? and `active` = ? and `osu_beatmapsets`.`deleted_at` is null and `active` = ? 124 Execute select count(*) as aggregate from `osu_beatmapsets` inner join `osu_favouritemaps` on `osu_favouritemaps`.`beatmapset_id` = `osu_beatmapsets`.`beatmapset_id` where `osu_favouritemaps`.`user_id` = 9 and `active` = 1 and `osu_beatmapsets`.`deleted_at` is null and `active` = 1 124 Close stmt 124 Prepare select count(*) as aggregate from `osu_beatmapsets` where `osu_beatmapsets`.`user_id` = ? and `osu_beatmapsets`.`user_id` is not null and `approved` = ? and `active` = ? and `osu_beatmapsets`.`deleted_at` is null and `active` = ? 124 Execute select count(*) as aggregate from `osu_beatmapsets` where `osu_beatmapsets`.`user_id` = 9 and `osu_beatmapsets`.`user_id` is not null and `approved` = -2 and `active` = 1 and `osu_beatmapsets`.`deleted_at` is null and `active` = 1 124 Close stmt 124 Prepare select * from `phpbb_user_group` where `phpbb_user_group`.`user_id` = ? and `phpbb_user_group`.`user_id` is not null 124 Execute select * from `phpbb_user_group` where `phpbb_user_group`.`user_id` = 9 and `phpbb_user_group`.`user_id` is not null 124 Close stmt 124 Prepare select count(*) as aggregate from `osu_beatmapsets` where `osu_beatmapsets`.`user_id` = ? and `osu_beatmapsets`.`user_id` is not null and `approved` = ? and `active` = ? and `osu_beatmapsets`.`deleted_at` is null and `active` = ? 124 Execute select count(*) as aggregate from `osu_beatmapsets` where `osu_beatmapsets`.`user_id` = 9 and `osu_beatmapsets`.`user_id` is not null and `approved` = 4 and `active` = 1 and `osu_beatmapsets`.`deleted_at` is null and `active` = 1 124 Close stmt 124 Prepare select * from `osu_user_month_playcount` where `osu_user_month_playcount`.`user_id` = ? and `osu_user_month_playcount`.`user_id` is not null 124 Execute select * from `osu_user_month_playcount` where `osu_user_month_playcount`.`user_id` = 9 and `osu_user_month_playcount`.`user_id` is not null 124 Close stmt 124 Prepare select `user_id`, `username_last` from `osu_username_change_history` where `osu_username_change_history`.`user_id` = ? and `osu_username_change_history`.`user_id` is not null and `type` in (?, ?, ?) and `username_last` is not null and `username_last` <> ? order by `timestamp` asc 124 Execute select `user_id`, `username_last` from `osu_username_change_history` where `osu_username_change_history`.`user_id` = 9 and `osu_username_change_history`.`user_id` is not null and `type` in ('support', 'paid', 'admin') and `username_last` is not null and `username_last` <> '' order by `timestamp` asc 124 Close stmt 124 Prepare select count(*) as aggregate from `osu_beatmapsets` where `osu_beatmapsets`.`user_id` = ? and `osu_beatmapsets`.`user_id` is not null and `approved` in (?, ?, ?) and `active` = ? and `osu_beatmapsets`.`deleted_at` is null and `active` = ? 124 Execute select count(*) as aggregate from `osu_beatmapsets` where `osu_beatmapsets`.`user_id` = 9 and `osu_beatmapsets`.`user_id` is not null and `approved` in (1, 2, 3) and `active` = 1 and `osu_beatmapsets`.`deleted_at` is null and `active` = 1 124 Close stmt 124 Prepare select * from `osu_user_replayswatched` where `osu_user_replayswatched`.`user_id` = ? and `osu_user_replayswatched`.`user_id` is not null 124 Execute select * from `osu_user_replayswatched` where `osu_user_replayswatched`.`user_id` = 9 and `osu_user_replayswatched`.`user_id` is not null 124 Close stmt 124 Prepare select count(*) as aggregate from `osu_scores_high` inner join `osu_leaders` on `osu_scores_high`.`score_id` = `osu_leaders`.`score_id` where `osu_leaders`.`user_id` = ? and exists (select * from `osu_beatmaps` where `osu_scores_high`.`beatmap_id` = `osu_beatmaps`.`beatmap_id` and `osu_beatmaps`.`deleted_at` is null) and (`hidden` = ?) 124 Execute select count(*) as aggregate from `osu_scores_high` inner join `osu_leaders` on `osu_scores_high`.`score_id` = `osu_leaders`.`score_id` where `osu_leaders`.`user_id` = 9 and exists (select * from `osu_beatmaps` where `osu_scores_high`.`beatmap_id` = `osu_beatmaps`.`beatmap_id` and `osu_beatmaps`.`deleted_at` is null) and (`hidden` = 0) 124 Close stmt 124 Prepare select count(*) as aggregate from `osu_scores` where `osu_scores`.`user_id` = ? and `osu_scores`.`user_id` is not null and exists (select * from `osu_beatmaps` where `osu_scores`.`beatmap_id` = `osu_beatmaps`.`beatmap_id` and `osu_beatmaps`.`deleted_at` is null) and `rank` <> 'F' 124 Execute select count(*) as aggregate from `osu_scores` where `osu_scores`.`user_id` = 9 and `osu_scores`.`user_id` is not null and exists (select * from `osu_beatmaps` where `osu_scores`.`beatmap_id` = `osu_beatmaps`.`beatmap_id` and `osu_beatmaps`.`deleted_at` is null) and `rank` <> 'F' 124 Close stmt 124 Prepare select * from `osu_user_stats` where `osu_user_stats`.`user_id` = ? and `osu_user_stats`.`user_id` is not null limit 1 124 Execute select * from `osu_user_stats` where `osu_user_stats`.`user_id` = 9 and `osu_user_stats`.`user_id` is not null limit 1 124 Close stmt 124 Prepare select count(*) as aggregate from `osu_user_stats` where `country_acronym` = ? and `rank_score` > (select `rank_score` from `osu_user_stats` where `user_id` = ?) 124 Execute select count(*) as aggregate from `osu_user_stats` where `country_acronym` = 'GB' and `rank_score` > (select `rank_score` from `osu_user_stats` where `user_id` = 9) 124 Close stmt 124 Prepare select * from `osu_user_donations` where `osu_user_donations`.`user_id` = ? and `osu_user_donations`.`user_id` is not null 124 Execute select * from `osu_user_donations` where `osu_user_donations`.`user_id` = 9 and `osu_user_donations`.`user_id` is not null 124 Close stmt 124 Prepare select count(*) as aggregate from `osu_beatmapsets` where `osu_beatmapsets`.`user_id` = ? and `osu_beatmapsets`.`user_id` is not null and `approved` in (?, ?) and `active` = ? and `osu_beatmapsets`.`deleted_at` is null and `active` = ? 124 Execute select count(*) as aggregate from `osu_beatmapsets` where `osu_beatmapsets`.`user_id` = 9 and `osu_beatmapsets`.`user_id` is not null and `approved` in (0, -1) and `active` = 1 and `osu_beatmapsets`.`deleted_at` is null and `active` = 1 124 Close stmt 124 Prepare select * from `osu_user_achievements` where `osu_user_achievements`.`user_id` = ? and `osu_user_achievements`.`user_id` is not null order by `date` desc 124 Execute select * from `osu_user_achievements` where `osu_user_achievements`.`user_id` = 9 and `osu_user_achievements`.`user_id` is not null order by `date` desc 124 Close stmt 124 Prepare select * from `osu_user_performance_rank` where `osu_user_performance_rank`.`user_id` = ? and `osu_user_performance_rank`.`user_id` is not null and `mode` = ? limit 1 124 Execute select * from `osu_user_performance_rank` where `osu_user_performance_rank`.`user_id` = 9 and `osu_user_performance_rank`.`user_id` is not null and `mode` = 0 limit 1 124 Close stmt 124 Prepare select * from `osu_counts` where `osu_counts`.`name` = ? limit 1 124 Execute select * from `osu_counts` where `osu_counts`.`name` = 'pp_rank_column' limit 1 124 Close stmt 124 Prepare select * from `phpbb_users` where `phpbb_users`.`user_id` = ? limit 1 124 Execute select * from `phpbb_users` where `phpbb_users`.`user_id` = 9 limit 1 124 Close stmt 124 Prepare select * from `osu_user_stats` where `osu_user_stats`.`user_id` = ? and `osu_user_stats`.`user_id` is not null limit 1 124 Execute select * from `osu_user_stats` where `osu_user_stats`.`user_id` = 9 and `osu_user_stats`.`user_id` is not null limit 1 124 Close stmt 124 Prepare select * from `osu_user_performance_rank` where `osu_user_performance_rank`.`user_id` = ? and `osu_user_performance_rank`.`user_id` is not null and `mode` = ? limit 1 124 Execute select * from `osu_user_performance_rank` where `osu_user_performance_rank`.`user_id` = 9 and `osu_user_performance_rank`.`user_id` is not null and `mode` = 0 limit 1 124 Close stmt 124 Prepare select * from `osu_counts` where `osu_counts`.`name` = ? limit 1 124 Execute select * from `osu_counts` where `osu_counts`.`name` = 'pp_rank_column' limit 1 124 Close stmt 124 Prepare select * from `phpbb_users` where `phpbb_users`.`user_id` = ? limit 1 124 Execute select * from `phpbb_users` where `phpbb_users`.`user_id` = 9 limit 1 124 Close stmt 124 Prepare select * from `osu_user_stats` where `osu_user_stats`.`user_id` = ? and `osu_user_stats`.`user_id` is not null limit 1 124 Execute select * from `osu_user_stats` where `osu_user_stats`.`user_id` = 9 and `osu_user_stats`.`user_id` is not null limit 1 124 Close stmt ```
notbakaneko commented 3 years ago

Also consider that we have additional security and authorization factors that have to be implemented as additional overhead, in addition to not simply allowing arbitrary query combinations for performance reasons; i.e. predictable query shapes are preferable.

peppy commented 3 years ago

Yep, obviously the queries would need to be limited to what is already indexed and available. Or at least be run on dedicated hardware paid for by something.

At the end of the day the overhead of actually surfacing more information than what we are already providing via endpoints would likely be the same or more, compared to just making rest endpoints.

LavaDesu commented 3 years ago

not simply allowing arbitrary query combinations

Definitely. We don't want people to execute potentially abusive queries. We can employ a few methods to alleviate this.

The depth of a query can be limited to a set amount. This avoids people abusing circular fields.

We can also assign a "cost" to each field (say, 1 for regular fields and 4 for fields which needs to fetch another model). If the total cost of the query exceeds a set limit, we can reject it.

The library I used (Lighthouse) have both of these as built-in options, but we can override their rules provider and add our own validation rules as well in case we need more complex rate-limiting (like a leaky bucket based on cost). There are also other methods to defend against expensive queries, a good resource is this page.

LavaDesu commented 2 years ago

I'm going to attempt this again. Currently I've got most of the base done, including auth, scopes, and complexity calculation. Things left to be done are rate-limiting and finally structuring the schema.

Should I open a draft PR or only open one after it's all completed?

peppy commented 2 years ago

Sounds fine, yes.