• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Fixed Rejecting a moderated user signup is slow (missing indexes)

Xon

Well-known member
#1
A staff mentioned that rejecting moderated user signups was getting quite slow. I rejected 1 spam user, and observed it taking ~17 seconds for the user to be rejected. Of this time, ~14.2 seconds is just 2 queries.

It looks like when a moderated user is rejected (ie deleted), the following two queries occur:

Code:
# 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';
Code:
# 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';
In both cases, it isn't using any indexes.

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:
Code:
alter table xf_user_alert add index `user_id` (`user_id`);
alter table xf_edit_history add index `edit_user_id` (`edit_user_id`);
 

Mike

XenForo developer
Staff member
#3
The indexes are probably worthwhile, but they're potentially beyond the scope of something we could add in a patch release (at least for a particularly large board). How many rows do you have in each table?
 

Xon

Well-known member
#4
The indexes are probably worthwhile, but they're potentially beyond the scope of something we could add in a patch release (at least for a particularly large board). How many rows do you have in each table?
Per the quoted MySQL slow query log:
xf_edit_history ~310000
xf_user_alert ~490000

It has only been ~3 months since migrating to a version of XF with the edit history. Of those alerts, only ~144000 are unviewed.

Adding the index to xf_edit_history only took ~20 seconds, and ~6 seconds for xf_user_alert. But that is obviously very hardware and data volume dependant.
 

Mike

XenForo developer
Staff member
#5
I have an example of a board with 3.6M rows in xf_edit_history so there may be a fair bit more delay with the alter there. (We generally only push the CLI upgrade explicitly for big boards for second point level releases.)
 

Mike

XenForo developer
Staff member
#7
I'm going to try adding these indexes for 1.4.5. Hopefully won't cause any upgrade issues, though it may take a few seconds to run the queries on larger forums.