[SlowQuery] Statuses (id, user_id, visibility, user_id) & train_checkins (status_id, departure) & unknown created_at

[MrKrisKrisu]

# Query_time: 2.725214  Lock_time: 0.000013  Rows_sent: 16  Rows_examined: 11866
# Rows_affected: 0  Bytes_sent: 0
# Tmp_tables: 1  Tmp_disk_tables: 1  Tmp_table_sizes: 319488
# Full_scan: No  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: Yes
# 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  statuses    range   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_visibility_index   9   NULL    3948    3945.00 100.00  100.00  Using index condition; Using temporary; Using filesort
# 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  100.00  Using index condition; Using where
#
SET timestamp=1716386735;
select `statuses`.* from `statuses` inner join `train_checkins` on `train_checkins`.`status_id` = `statuses`.`id` where `train_checkins`.`departure` < 'xxx' and `statuses`.`user_id` in (xxx, xxx, xxx, xxx) and 1 = 1 and `statuses`.`visibility` in (0, 2, 4) or `statuses`.`user_id` = xxx order by `train_checkins`.`departure` desc, `created_at` desc limit 16 offset 15;

[MrKrisKrisu]

Use Cursor pagination for improved performance

[jeyemwey]

For anyone wondering, this is the main dashboard query which will be limited to followers "non-private" statuses and your own statuses, and sort all of them by departure time, (desc) with a limit and an offset.