It looks like when a moderated user is rejected (ie deleted), the following two queries occur:
# Query_time: 11.764225 Lock_time: 0.000027 Rows_sent: 0 Rows_examined: 318519 SET timestamp=1420733968; UPDATE IGNORE xf_edit_history SET edit_user_id = 0 WHERE edit_user_id = '312133';
# Query_time: 2.560013 Lock_time: 0.000020 Rows_sent: 0 Rows_examined: 493988 UPDATE IGNORE xf_user_alert SET user_id = 0,username = 'SpamSignup' WHERE user_id = '312133';
The user revert functionality (XenForo_Model_EditHistory::getEditHistoryByUserSinceDate) manages to avoid this as it matches on the edit_date index.
I've added the following indexes which help:
alter table xf_user_alert add index `user_id` (`user_id`); alter table xf_edit_history add index `edit_user_id` (`edit_user_id`);