#82 Slow display of the /all timeline

On my instance, it's been a while now (don't remember when it got like that) since the instance.tld/user/all timelines are taking 10+ seconds to load, sometimes a bit more. I would like to investigate why it is taking so long, which process can be improved?

Here's the slow log query:

# Time: 191005 17:15:53
# User@Host: social[social] @ localhost []
# Thread_id: 215  Schema: social  QC_hit: No
# Query_time: 12.510003  Lock_time: 0.000614  Rows_sent: 200  Rows_examined: 4209204
# Rows_affected: 0  Bytes_sent: 2471
SET timestamp=1570288553;
SELECT  id  
 FROM   `notice`   

NATURAL INNER JOIN ((SELECT id FROM notice WHERE profile_id IN (SELECT subscribed FROM subscription WHERE subscriber = 1)) UNION (SELECT notice_id AS id FROM reply WHERE profile_id = 1) UNION (SELECT notice_id AS id FROM attention WHERE profile_id = 1) UNION (SELECT notice_id AS id FROM group_inbox WHERE group_id IN (SELECT group_id FROM group_member WHERE profile_id = 1))) AS t1 
 WHERE ( notice.created > TIMESTAMP '2015-01-14 12:03:10' )  AND ( scope <> 16 ) 
 
 
 ORDER BY id DESC  
 LIMIT 0, 200;