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