Hi alll
When I view a user profile, the queries for user post take forever ?
My database statics:
Post: 8 mils rows
mentions_posts : 3 mils rows
notifications: 3 mils rows
users_discussions: 3 mils rows
Here is the log:
# Time: 170723 0:09:00
# Thread_id: 563 Schema: forum QC_hit: No
# Query_time: 1.105699 Lock_time: 0.000027 Rows_sent: 33333 Rows_examined: 3474501
# Rows_affected: 0
use forum;
SET timestamp=1500768540;
select `posts`.*, `mentions_posts`.`mentions_id` as `pivot_mentions_id`, `mentions_posts`.`post_id` as `pivot_post_id` from `posts` inner join `mentions_posts` on `posts`.`id` = `mentions_posts`.`post_id` where `type` in ('comment', 'discussionRenamed', 'discussionLocked', 'discussionStickied', 'discussionTagged') and `mentions_posts`.`mentions_id` in ('1', '981225', '981867', '5653904', '3745849', '3745848', '3745827', '3965156', '6037332');
# Time: 170723 0:11:06
# User@Host: flarum[flarum] @ localhost []
# Thread_id: 565 Schema: forum QC_hit: No
# Query_time: 6.548220 Lock_time: 0.000021 Rows_sent: 0 Rows_examined: 3447007
# Rows_affected: 0
SET timestamp=1500768666;
select * from `notifications` where `notifications`.`user_id` = '1' and `notifications`.`user_id` is not null and `type` in ('discussionRenamed', 'postLiked', 'discussionLocked', 'postMentioned', 'userMentioned', 'newPost') and `is_read` = '0' and `is_deleted` = '0';
# Time: 170723 0:11:34
# User@Host: flarum[flarum] @ localhost []
# Thread_id: 568 Schema: forum QC_hit: No
# Query_time: 26.450297 Lock_time: 0.000021 Rows_sent: 9 Rows_examined: 7949541
# Rows_affected: 0
SET timestamp=1500768694;
select `id` from `posts` where `type` in ('comment', 'discussionRenamed', 'discussionLocked', 'discussionStickied', 'discussionTagged') and `user_id` = '1' and `type` = 'comment' order by `time` desc limit 20 offset 0;
# Time: 170723 0:11:35
# User@Host: flarum[flarum] @ localhost []
# Thread_id: 568 Schema: forum QC_hit: No
# Query_time: 1.113324 Lock_time: 0.000026 Rows_sent: 33335 Rows_examined: 3474514
# Rows_affected: 0
SET timestamp=1500768695;
select `posts`.*, `mentions_posts`.`mentions_id` as `pivot_mentions_id`, `mentions_posts`.`post_id` as `pivot_post_id` from `posts` inner join `mentions_posts` on `posts`.`id` = `mentions_posts`.`post_id` where `type` in ('comment', 'discussionRenamed', 'discussionLocked', 'discussionStickied', 'discussionTagged') and `mentions_posts`.`mentions_id` in ('1', '981225', '981867', '5653904', '3745849', '3745848', '3745827', '3965156', '6037332');
How to improve the queries?