[SlowQuery - 14 sec] select statuses.user_id, SUM(train_checkins.points) [...]

[MrKrisKrisu]

Raw query

SELECT `statuses`.`user_id`,
       Sum(train_checkins.points)
       AS points,
       Sum(train_checkins.distance)
       AS distance,
       Sum(Timestampdiff(minute, train_checkins.departure,
           train_checkins.arrival)) AS
       duration,
       Sum(train_checkins.distance) / ( Sum(Timestampdiff(minute,
                                            train_checkins.departure,
                                            train_checkins.arrival)) /
                                                 60 )
       AS speed
FROM   `statuses`
       INNER JOIN `train_checkins`
               ON `train_checkins`.`status_id` = `statuses`.`id`
       INNER JOIN `users`
               ON `statuses`.`user_id` = `users`.`id`
WHERE  `train_checkins`.`departure` >= 'xxx'
       AND `train_checkins`.`departure` <= 'xxx'
       AND ( `users`.`private_profile` = 0 )
GROUP  BY `statuses`.`user_id`
ORDER  BY `points` DESC
LIMIT  20; 

Slow log

# Query_time: 14.011299  Lock_time: 0.000081  Rows_sent: 20  Rows_examined: 3882535
# Rows_affected: 0  Bytes_sent: 0
# Tmp_tables: 1  Tmp_disk_tables: 0  Tmp_table_sizes: 255040
# Full_scan: Yes  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: Yes
#
# explain: id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
# explain: 1	SIMPLE	users	index	PRIMARY	PRIMARY	8	NULL	7262	6861.00	100.00	90.19	Using where; Using temporary; Using filesort
# explain: 1	SIMPLE	statuses	ref	PRIMARY,statuses_user_id_foreign,statuses_user_id_IDX,statuses_user_id_mastodon_post_id_created_at_index,statuses_user_id_visibility_index	statuses_user_id_foreign	8	traewelling.users.id	134	313.05	100.00	100.00	Using index
# explain: 1	SIMPLE	train_checkins	eq_ref	train_checkins_status_id_unique,train_checkins_departure_arrival_status_id_index	train_checkins_status_id_unique	8	traewelling.statuses.id	1	1.00	100.00	1.64	Using where
#

Generating code

traewelling/app/Http/Controllers/Backend/LeaderboardController.php

Lines 40 to 70 in ef863d7

	$query = DB::table('statuses')
	->join('train_checkins', 'train_checkins.status_id', '=', 'statuses.id')
	->join('users', 'statuses.user_id', '=', 'users.id')
	->where('train_checkins.departure', '>=', $since->toIso8601String())
	->where('train_checkins.departure', '<=', $until->toIso8601String())
	->where(function(Builder $query) {
	$query->where('users.private_profile', 0);
	if (auth()->check()) {
	$query->orWhereIn('users.id', auth()->user()->follows->pluck('id'))
	->orWhere('users.id', auth()->user()->id);
	}
	})
	->groupBy('statuses.user_id')
	->select([
	'statuses.user_id',
	DB::raw('SUM(train_checkins.points) AS points'),
	DB::raw($sumDistance . ' AS distance'),
	DB::raw(self::getDurationSelector() . ' AS duration'),
	DB::raw($sumDistance . ' / (' . self::getDurationSelector() . ' / 60) AS speed'),
	])
	->orderByDesc($orderBy)
	->limit($limit);
	if ($onlyFollowings && auth()->check()) {
	$query->where(function($query) {
	$query->whereIn('statuses.user_id', auth()->user()->follows->pluck('id'))
	->orWhere('statuses.user_id', auth()->user()->id);
	});
	}
	$data = $query->get();

[MrKrisKrisu]

This query is cached, but should still be improved.

traewelling/app/Http/Controllers/Frontend/LeaderboardController.php

Lines 38 to 44 in ef863d7

	$usersLeaderboard = Cache::remember(
	CacheKey::LEADERBOARD_GLOBAL_POINTS,
	$ttl,
	static fn() => LeaderboardBackend::getLeaderboard()
	)->filter(function(stdClass $row) {
	return Gate::allows('view', $row->user);
	});

[HerrLevin]

We could rework our points-System so that the current scoreboard is not a rolling sum but rather a sum for the current week, just like swarm does it.

Would that be an option?