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

Making deletion of user-accounts faster

Discussion in 'XenForo Suggestions' started by Bugfix, Feb 18, 2016.

  1. Bugfix

    Bugfix Member

    Hello!
    With a big installation the deletion of a user takes about 10sec.
    We found the problem within 3 tables and optimized it with 3 indexes:

    Code:
    5,8 sec.: UPDATE IGNORE xf_conversation_master SET last_message_user_id = 0,last_message_username = 'deletedusername' WHERE last_message_user_id = ?
    3,5 Sec.: UPDATE IGNORE xf_conversation_user SET last_message_user_id = 0,last_message_username = 'deletedusername' WHERE last_message_user_id = ?
    1,5 Sec.: UPDATE IGNORE xengallery_comment SET user_id = 0,username = 'deletedusername' WHERE user_id = ?
    We added these 3 indexes:
    Code:
    ALTER TABLE `xf_conversation_master` ADD INDEX ( `last_message_user_id` );
    ALTER TABLE `xf_conversation_user` ADD INDEX ( `last_message_user_id` );
    ALTER TABLE `xengallery_comment` ADD INDEX ( `user_id` );
    
    Now, the deletion takes about 1sec.
    Maybe this helps someone for optimisation!

    regards H.
     
  2. Chris D

    Chris D XenForo Developer Staff Member

    We've added this one in the next XFMG release:
    Code:
    ALTER TABLE `xengallery_comment` ADD INDEX ( `user_id` );
    The others are actually a bigger consideration due to the potential overhead of those indexes vs the frequency in which they'd be used - deleting users is generally quite uncommon so this taking 10 seconds isn't particularly a big deal. That said, of course, you've made the change yourself which is perfectly valid if you have a use case which would need that to happen more frequently.
     
  3. Bugfix

    Bugfix Member

    Thank you.
    Yes, we have very much spam users or fake registrations and want to keep to regs clean. And the table xf_conversation_master has about 8 Mio rows - that's beacause this sql is so slow.
    I think if there are less rows this won't be so slowly.
     

Share This Page