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

Discussion in 'Resolved Bug Reports' started by Xon, Jan 8, 2015.

  1. Xon

    Xon Well-Known Member

    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`);
    Liam W, Walter, jOOc and 2 others like this.
  2. semprot

    semprot Active Member

    brilliant (y)
  3. Mike

    Mike XenForo Developer Staff Member

    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?
  4. Xon

    Xon Well-Known Member

    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.
  5. Mike

    Mike XenForo Developer Staff Member

    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.)
    Xon likes this.
  6. Stuart Wright

    Stuart Wright Well-Known Member

    The alter for our edit history of 320,000 rows took 10 seconds.
    Xon likes this.
  7. Mike

    Mike XenForo Developer Staff Member

    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.
    semprot and Xon like this.

Share This Page