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?
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?
# 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;
Here's the slow log query:
```log
# 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;
```
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: