[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

https://github.com/Traewelling/traewelling/blob/ef863d7e4fa67364644e27775bb8e0bc7c0b2c47/app/Http/Controllers/Backend/LeaderboardController.php#L40-L70

[MrKrisKrisu]

This query is cached, but should still be improved.

https://github.com/Traewelling/traewelling/blob/ef863d7e4fa67364644e27775bb8e0bc7c0b2c47/app/Http/Controllers/Frontend/LeaderboardController.php#L38-L44

[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?