[Very Slow and locking Query] Global Dashboard

[MrKrisKrisu]

This query is very hard to our database. For some very active users the query needs > 10 secs to be executed and also causes table locks, which freeze our whole system.

Raw query

SELECT `statuses`.*
FROM   `statuses`
       INNER JOIN `train_checkins`
               ON `train_checkins`.`status_id` = `statuses`.`id`
       INNER JOIN `users`
               ON `statuses`.`user_id` = `users`.`id`
WHERE  ( ( `users`.`private_profile` = 0
           AND `visibility` IN ( 0, 4 ) )
          OR `users`.`id` = xxx
          OR ( `users`.`id` IN (SELECT `follow_id`
                                FROM   `users`
                                       INNER JOIN `follows`
                                               ON `users`.`id` =
                                                  `follows`.`follow_id`
                                WHERE  `follows`.`user_id` = xxx)
               AND `statuses`.`visibility` NOT IN ( 1, 3 ) ) )
       AND `users`.`shadow_banned` = 0
       AND `train_checkins`.`departure` < 'xxx'
       AND `statuses`.`user_id` NOT IN (SELECT `muted_id`
                                        FROM   `users`
                                               INNER JOIN `user_mutes`
                                                       ON `users`.`id` =
`user_mutes`.`muted_id`
WHERE  `user_mutes`.`user_id` = xxx)
AND `statuses`.`user_id` NOT IN (SELECT `blocked_id`
FROM   `users`
INNER JOIN `user_blocks`
ON `users`.`id` =
`user_blocks`.`blocked_id`
WHERE  `user_blocks`.`user_id` = xxx)
AND `statuses`.`user_id` NOT IN (SELECT `user_id`
FROM   `users`
INNER JOIN `user_blocks`
ON `users`.`id` =
`user_blocks`.`user_id`
WHERE  `user_blocks`.`blocked_id` = xxx)
ORDER  BY `train_checkins`.`departure` DESC
LIMIT  16 offset 0 

Generating code

https://github.com/Traewelling/traewelling/blob/9bd816d8cce228e662f00a6a3033fdc8f29364cd/app/Http/Controllers/Backend/User/DashboardController.php#L41-L79