Calendar Fixed Lots of slow SQL queries impacting user experience

Affected version
2.4.0

Mouth

Well-Known Member
The below is from my mysql-slow.log, an example of lots of Calendar queries taking 3-6 secs to complete. No other add-ons appearing in the slow log.
What can be done to improve these and have them stop taking so long?

Code:
# Time: 2021-05-25T11:19:45.029564+10:00
# [email protected]: netrider[netrider] @ localhost []  Id: 181002
# Schema: netrider  Last_errno: 0  Killed: 0
# Query_time: 3.153228  Lock_time: 0.000086  Rows_sent: 5  Rows_examined: 26690  Rows_affected: 0  Bytes_sent: 37164
SET timestamp=1621905585;
SELECT `xf_nf_calendar_event`.*, `xf_nf_calendar_calendar_Calendar_1`.*, `xf_user_User_2`.*, `xf_nf_calendar_event_response_UserResponse_3`.*, `xf_attachment_CoverImage_4`.*, `xf_attachment_data_Data_5`.*, `xf_permission_cache_content_Permissions_6`.*
                        FROM `xf_nf_calendar_event`
                        LEFT JOIN `xf_nf_calendar_calendar` AS `xf_nf_calendar_calendar_Calendar_1` ON (`xf_nf_calendar_calendar_Calendar_1`.`category_id` = `xf_nf_calendar_event`.`category_id`)
LEFT JOIN `xf_user` AS `xf_user_User_2` ON (`xf_user_User_2`.`user_id` = `xf_nf_calendar_event`.`user_id`)
LEFT JOIN `xf_nf_calendar_event_response` AS `xf_nf_calendar_event_response_UserResponse_3` ON (`xf_nf_calendar_event_response_UserResponse_3`.`user_id` = 0 AND `xf_nf_calendar_event_response_UserResponse_3`.`event_id` = `xf_nf_calendar_event`.`event_id`)
LEFT JOIN `xf_attachment` AS `xf_attachment_CoverImage_4` ON (`xf_attachment_CoverImage_4`.`content_type` = 'nf_calendar_event' AND `xf_attachment_CoverImage_4`.`content_id` = `xf_nf_calendar_event`.`event_id` AND `xf_attachment_CoverImage_4`.`attachment_id` = `xf_nf_calendar_event`.`cover_image_id`)
LEFT JOIN `xf_attachment_data` AS `xf_attachment_data_Data_5` ON (`xf_attachment_data_Data_5`.`data_id` = `xf_attachment_CoverImage_4`.`data_id`)
LEFT JOIN `xf_permission_cache_content` AS `xf_permission_cache_content_Permissions_6` ON (`xf_permission_cache_content_Permissions_6`.`content_type` = 'nf_calendar' AND `xf_permission_cache_content_Permissions_6`.`content_id` = `xf_nf_calendar_calendar_Calendar_1`.`category_id` AND `xf_permission_cache_content_Permissions_6`.`permission_combination_id` = '1')
                        WHERE (`xf_nf_calendar_event`.`event_state` = 'visible') AND (`xf_nf_calendar_event`.`category_id` = 5) AND (`xf_nf_calendar_event`.`event_id` <> 2577)
                        ORDER BY `xf_nf_calendar_event`.`last_update` DESC
                
LIMIT 5;
# Time: 2021-05-25T11:36:19.725921+10:00
# [email protected]: netrider[netrider] @ localhost []  Id: 181400
# Schema: netrider  Last_errno: 0  Killed: 0
# Query_time: 5.393617  Lock_time: 0.000094  Rows_sent: 5  Rows_examined: 38814  Rows_affected: 0  Bytes_sent: 33505
SET timestamp=1621906579;
SELECT `xf_nf_calendar_event`.*, `xf_nf_calendar_calendar_Calendar_1`.*, `xf_user_User_2`.*, `xf_nf_calendar_event_response_UserResponse_3`.*, `xf_attachment_CoverImage_4`.*, `xf_attachment_data_Data_5`.*, `xf_permission_cache_content_Permissions_6`.*
                        FROM `xf_nf_calendar_event`
                        LEFT JOIN `xf_nf_calendar_calendar` AS `xf_nf_calendar_calendar_Calendar_1` ON (`xf_nf_calendar_calendar_Calendar_1`.`category_id` = `xf_nf_calendar_event`.`category_id`)
LEFT JOIN `xf_user` AS `xf_user_User_2` ON (`xf_user_User_2`.`user_id` = `xf_nf_calendar_event`.`user_id`)
LEFT JOIN `xf_nf_calendar_event_response` AS `xf_nf_calendar_event_response_UserResponse_3` ON (`xf_nf_calendar_event_response_UserResponse_3`.`user_id` = 0 AND `xf_nf_calendar_event_response_UserResponse_3`.`event_id` = `xf_nf_calendar_event`.`event_id`)
LEFT JOIN `xf_attachment` AS `xf_attachment_CoverImage_4` ON (`xf_attachment_CoverImage_4`.`content_type` = 'nf_calendar_event' AND `xf_attachment_CoverImage_4`.`content_id` = `xf_nf_calendar_event`.`event_id` AND `xf_attachment_CoverImage_4`.`attachment_id` = `xf_nf_calendar_event`.`cover_image_id`)
LEFT JOIN `xf_attachment_data` AS `xf_attachment_data_Data_5` ON (`xf_attachment_data_Data_5`.`data_id` = `xf_attachment_CoverImage_4`.`data_id`)
LEFT JOIN `xf_permission_cache_content` AS `xf_permission_cache_content_Permissions_6` ON (`xf_permission_cache_content_Permissions_6`.`content_type` = 'nf_calendar' AND `xf_permission_cache_content_Permissions_6`.`content_id` = `xf_nf_calendar_calendar_Calendar_1`.`category_id` AND `xf_permission_cache_content_Permissions_6`.`permission_combination_id` = '1')
                        WHERE (`xf_nf_calendar_event`.`event_state` = 'visible') AND (`xf_nf_calendar_event`.`category_id` = 2) AND (`xf_nf_calendar_event`.`event_id` <> 998)
                        ORDER BY `xf_nf_calendar_event`.`last_update` DESC
                
LIMIT 5;
# Time: 2021-05-25T11:36:26.583787+10:00
# [email protected]: netrider[netrider] @ localhost []  Id: 181400
# Schema: netrider  Last_errno: 0  Killed: 0
# Query_time: 6.856547  Lock_time: 0.000098  Rows_sent: 5  Rows_examined: 50052  Rows_affected: 0  Bytes_sent: 28095
SET timestamp=1621906586;
SELECT `xf_nf_calendar_event`.*, `xf_nf_calendar_calendar_Calendar_1`.*, `xf_user_User_2`.*, `xf_nf_calendar_event_response_UserResponse_3`.*, `xf_attachment_CoverImage_4`.*, `xf_attachment_data_Data_5`.*, `xf_permission_cache_content_Permissions_6`.*
                        FROM `xf_nf_calendar_event`
                        LEFT JOIN `xf_nf_calendar_calendar` AS `xf_nf_calendar_calendar_Calendar_1` ON (`xf_nf_calendar_calendar_Calendar_1`.`category_id` = `xf_nf_calendar_event`.`category_id`)
LEFT JOIN `xf_user` AS `xf_user_User_2` ON (`xf_user_User_2`.`user_id` = `xf_nf_calendar_event`.`user_id`)
LEFT JOIN `xf_nf_calendar_event_response` AS `xf_nf_calendar_event_response_UserResponse_3` ON (`xf_nf_calendar_event_response_UserResponse_3`.`user_id` = 0 AND `xf_nf_calendar_event_response_UserResponse_3`.`event_id` = `xf_nf_calendar_event`.`event_id`)
LEFT JOIN `xf_attachment` AS `xf_attachment_CoverImage_4` ON (`xf_attachment_CoverImage_4`.`content_type` = 'nf_calendar_event' AND `xf_attachment_CoverImage_4`.`content_id` = `xf_nf_calendar_event`.`event_id` AND `xf_attachment_CoverImage_4`.`attachment_id` = `xf_nf_calendar_event`.`cover_image_id`)
LEFT JOIN `xf_attachment_data` AS `xf_attachment_data_Data_5` ON (`xf_attachment_data_Data_5`.`data_id` = `xf_attachment_CoverImage_4`.`data_id`)
LEFT JOIN `xf_permission_cache_content` AS `xf_permission_cache_content_Permissions_6` ON (`xf_permission_cache_content_Permissions_6`.`content_type` = 'nf_calendar' AND `xf_permission_cache_content_Permissions_6`.`content_id` = `xf_nf_calendar_calendar_Calendar_1`.`category_id` AND `xf_permission_cache_content_Permissions_6`.`permission_combination_id` = '1')
                        WHERE (`xf_nf_calendar_event`.`event_state` = 'visible') AND (`xf_nf_calendar_event`.`user_id` = 2) AND (`xf_nf_calendar_event`.`event_id` <> 998) AND (`xf_nf_calendar_event`.`event_id` NOT IN (3008, 3003, 2999, 2998, 2997))
                        ORDER BY `xf_nf_calendar_event`.`last_update` DESC
                
LIMIT 5;
 

Nix

Administrator
Staff member
Thanks. I've slimmed this down in the next version.

Specifically looks like it was attempting to join user responses to events when it shouldn't have been.
 
Top