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

[Kirby]

Affected version

2.1.0 Beta 4

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.

 

[Xon]

This has been reported before; https://xenforo.com/community/threads/deleting-users-is-very-slow.122856/post-1113008

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/

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.

 

[Chris D]

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]

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]

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

 

[Chris D]

The bulk of these, and some others, have been/are being added to XF 2.2 so we'll close the bug report now.