JustinHawk
New Member
AMS Premium
CAS Premium
IMS Premium
LD Premium
RMS Premium
SC Premium
UBS Premium
We are facing some performance issues with this addon MariaDB. We have more than 90K articles published. Problem happens generally only on the first page of categories. Other than that i have already optimized and brought it down to like 0.001 sec But category first page is slow but bearable. To speed it up, i setup an automation at my end, which works great. But i would really appreciate if you have any more insights or advice you would like to share with me.
Code:
# Thread_id: 4614988 Schema: sql_thcom QC_hit: No
# Query_time: 70.884838 Lock_time: 0.000012 Rows_sent: 0 Rows_examined: 96022
# Rows_affected: 0 Bytes_sent: 0
SET timestamp=1721009472;
SELECT `xf_xa_ams_article`.*, `xf_user_User_1`.*
FROM `xf_xa_ams_article`
LEFT JOIN `xf_user` AS `xf_user_User_1` ON (`xf_user_User_1`.`user_id` = `xf_xa_ams_article`.`user_id`)
WHERE (`xf_xa_ams_article`.`article_state` = 'awaiting') AND (`xf_xa_ams_article`.`publish_date` <= 1721009401);
Code:
# Query_time: 10.770874 Lock_time: 0.000015 Rows_sent: 3 Rows_examined: 96039
# Rows_affected: 0 Bytes_sent: 421888
SET timestamp=1721009503;
SELECT `xf_xa_ams_article`.*, `xf_user_User_1`.*, `xf_xa_ams_category_Category_2`.*
FROM `xf_xa_ams_article`
LEFT JOIN `xf_user` AS `xf_user_User_1` ON (`xf_user_User_1`.`user_id` = `xf_xa_ams_article`.`user_id`)
LEFT JOIN `xf_xa_ams_category` AS `xf_xa_ams_category_Category_2` ON (`xf_xa_ams_category_Category_2`.`category_id` = `xf_xa_ams_article`.`category_id`)
LEFT JOIN `xf_permission_cache_content` AS `xf_permission_cache_content_Permissions_3` ON (`xf_permission_cache_content_Permissions_3`.`content_type` = 'ams_category' AND `xf_permission_cache_content_Permissions_3`.`content_id` = `xf_xa_ams_category_Category_2`.`category_id` AND `xf_permission_cache_content_Permissions_3`.`permission_combination_id` = '1')
WHERE (`xf_xa_ams_article`.`last_comment_date` > 0) AND (`xf_xa_ams_article`.`article_state` <> 'deleted') AND (`xf_xa_ams_category_Category_2`.`allow_comments` = 1) AND (`xf_xa_ams_article`.`last_comment_date` > 1718417492)
ORDER BY `xf_xa_ams_article`.`last_comment_date` DESC, `xf_xa_ams_article`.`article_id` DESC
LIMIT 20;
Code:
# Thread_id: 4615117 Schema: sql_thecom QC_hit: No
# Query_time: 26.571596 Lock_time: 0.000035 Rows_sent: 0 Rows_examined: 52509
# Rows_affected: 0 Bytes_sent: 24576
SET timestamp=1721009500;
SELECT `xf_xa_ams_article`.*, `xf_user_User_1`.*, `xf_xa_ams_article_feature_Featured_2`.*, `xf_attachment_CoverImage_3`.*, `xf_attachment_data_Data_4`.*, `xf_xa_ams_article_read_Read_5`.*, `xf_xa_ams_article_watch_Watch_6`.*, `xf_reaction_content_Reactions_7`.*, `xf_bookmark_item_Bookmarks_8`.*, `xf_xa_ams_category_Category_9`.*, `xf_xa_ams_category_watch_Watch_10`.*, `xf_deletion_log_DeletionLog_11`.*
FROM `xf_xa_ams_article`
LEFT JOIN `xf_user` AS `xf_user_User_1` ON (`xf_user_User_1`.`user_id` = `xf_xa_ams_article`.`user_id`)
LEFT JOIN `xf_xa_ams_article_feature` AS `xf_xa_ams_article_feature_Featured_2` ON (`xf_xa_ams_article_feature_Featured_2`.`article_id` = `xf_xa_ams_article`.`article_id`)
LEFT JOIN `xf_attachment` AS `xf_attachment_CoverImage_3` ON (`xf_attachment_CoverImage_3`.`content_type` = 'ams_article' AND `xf_attachment_CoverImage_3`.`content_id` = `xf_xa_ams_article`.`article_id` AND `xf_attachment_CoverImage_3`.`attachment_id` = `xf_xa_ams_article`.`cover_image_id`)
LEFT JOIN `xf_attachment_data` AS `xf_attachment_data_Data_4` ON (`xf_attachment_data_Data_4`.`data_id` = `xf_attachment_CoverImage_3`.`data_id`)
LEFT JOIN `xf_xa_ams_article_read` AS `xf_xa_ams_article_read_Read_5` ON (`xf_xa_ams_article_read_Read_5`.`article_id` = `xf_xa_ams_article`.`article_id` AND `xf_xa_ams_article_read_Read_5`.`user_id` = '1')
LEFT JOIN `xf_xa_ams_article_watch` AS `xf_xa_ams_article_watch_Watch_6` ON (`xf_xa_ams_article_watch_Watch_6`.`article_id` = `xf_xa_ams_article`.`article_id` AND `xf_xa_ams_article_watch_Watch_6`.`user_id` = '1')
LEFT JOIN `xf_reaction_content` AS `xf_reaction_content_Reactions_7` ON (`xf_reaction_content_Reactions_7`.`content_type` = 'ams_article' AND `xf_reaction_content_Reactions_7`.`content_id` = `xf_xa_ams_article`.`article_id` AND `xf_reaction_content_Reactions_7`.`reaction_user_id` = '1')
LEFT JOIN `xf_bookmark_item` AS `xf_bookmark_item_Bookmarks_8` ON (`xf_bookmark_item_Bookmarks_8`.`content_type` = 'ams_article' AND `xf_bookmark_item_Bookmarks_8`.`content_id` = `xf_xa_ams_article`.`article_id` AND `xf_bookmark_item_Bookmarks_8`.`user_id` = '1')
LEFT JOIN `xf_xa_ams_category` AS `xf_xa_ams_category_Category_9` ON (`xf_xa_ams_category_Category_9`.`category_id` = `xf_xa_ams_article`.`category_id`)
LEFT JOIN `xf_xa_ams_category_watch` AS `xf_xa_ams_category_watch_Watch_10` ON (`xf_xa_ams_category_watch_Watch_10`.`category_id` = `xf_xa_ams_category_Category_9`.`category_id` AND `xf_xa_ams_category_watch_Watch_10`.`user_id` = '1')
LEFT JOIN `xf_deletion_log` AS `xf_deletion_log_DeletionLog_11` ON (`xf_deletion_log_DeletionLog_11`.`content_type` = 'ams_article' AND `xf_deletion_log_DeletionLog_11`.`content_id` = `xf_xa_ams_article`.`article_id`)
WHERE (`xf_xa_ams_article`.`category_id` IN (2)) AND ((`xf_xa_ams_article`.`article_state` IN ('visible', 'deleted', 'moderated'))) AND (`xf_xa_ams_article`.`sticky` = 1) AND (`xf_xa_ams_article`.`category_id` = 2)
ORDER BY `xf_xa_ams_article`.`publish_date` DESC;
