Calendar Fixed Lots of slow SQL queries impacting user experience

Affected version
2.4.0
Mouth

Mouth

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
# User@Host: 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
# User@Host: 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
# User@Host: 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;
 
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.
 
You must log in or register to reply here.
Back
Top