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

[Xon]

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:

# 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';

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:

alter table xf_user_alert add index `user_id` (`user_id`);
alter table xf_edit_history add index `edit_user_id` (`edit_user_id`);

 

[HappyWorld]

brilliant

 

[Mike]

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]

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]

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.)

 

[Stuart Wright]

The alter for our edit history of 320,000 rows took 10 seconds.

 

[Mike]

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.