#82 Slow display of the /all timeline

Open
opened 4 years ago by postblue · 1 comments

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?
Guillaume commented 4 years ago
Poster

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;
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; ```
Sign in to join this conversation.
No Milestone
No assignee
1 Participants
Loading...
Cancel
Save
There is no content yet.