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