\XF\Service\User\DeleteCleanUp causes full table scans

Affected version
2.1.0 Beta 4

Kirby

Well-known member
We did a mass user cleanup (about 120K records) and I noticed that the cleanup jobs were extremly slow, it seemd like it would take forever to complete :)

So I took a look as to why it did take so long and noticed that it does full table scans on a couple tables, epecially some that had a few 100K records,

I am not sure if it's worth considering that, but adding indexes on
  • xf_mg_media_item.last_comment_user_id
  • xf_mg_album.last_comment_user_id
  • xf_moderator_log.content_user_id
  • xf_conversation_master.last_message_user_id
  • xf_conversation_user.last_message_user_id
(We've had like 500K media items)

Significantly speeeded up things, a remaining chunk of ~80K jobs was completed within a few minutes while the previous 40K took over an hour.
189745
 
Last edited:

Xon

Well-known member
This has been reported before; https://xenforo.com/community/threads/deleting-users-is-very-slow.122856/post-1113008
SQL:
ALTER TABLE xf_conversation_master ADD INDEX ( last_message_user_id );
ALTER TABLE xf_conversation_user ADD INDEX ( last_message_user_id );
ALTER TABLE xf_thread ADD INDEX ( last_post_user_id );
ALTER TABLE xf_warning ADD INDEX ( warning_user_id );
ALTER TABLE xf_report ADD INDEX ( last_modified_user_id );
I identified some extra columns that can also speed up deleting users, and bundled it into my User Essentials add-on (for XF1/XF2); https://xenforo.com/community/resources/user-essentials.6272/update/27920/
SQL:
alter table xf_thread add index last_post_user_id (last_post_user_id);
alter table xf_report add index last_modified_user_id (last_modified_user_id);
alter table xf_warning add index warning_user_id (warning_user_id);
alter table xf_conversation_master add index last_message_user_id( last_message_user_id);
alter table xf_conversation_user add index last_message_user_id (last_message_user_id);
alter table xf_tag_content add index add_user_id (add_user_id);
alter table xf_moderator_log add index content_user_id (content_user_id);
I think some of these have been added to XF over time, but not the ones you've identified.
 
Last edited:

Chris D

XenForo developer
Staff member
SQL:
alter table xf_mg_media_item add index xf_mg_media_item (xf_mg_media_item);
You might want to check that one if that's what you're actually using. There is no column named xf_mg_media_item.
 

Xon

Well-known member
You might want to check that one if that's what you're actually using. There is no column named xf_mg_media_item.
You are right, that was a copy & paste error since I was reconstructing the query a while ago. IIRC some indexes where added to that table which helped some table scans a while ago so I removed (conditionally) adding that index in my add-on.
 

Kirby

Well-known member
Another one involved in user deletion (though not cleanup and probably also triggered in other caes)
xf_user.permission_combination_id
 
Top