Lack of interest Making deletion of user-accounts faster

[Bugfix]

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:

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:

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.

 

[Chris D]

We've added this one in the next XFMG release:

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.

 

[Bugfix]

We've added this one in the next XFMG release:

Thank you.

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.

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.

 

[gotski]

I ran into the same problem today when I deleted 500 accounts, the database just hung up with requests

| 238541533 | database_base | localhost | database_base | Execute | 382 | updating | UPDATE IGNORE xf_conversation_user SET
last_message_user_id = 0, last_message_username = 'Iris6897'
WHERE last_message_user_id = '375957' |
| 238541535 | database_base | localhost | database_base | Execute | 382 | updating | UPDATE IGNORE xf_conversation_user SET
last_message_user_id = 0, last_message_username = 'Erica2818'
WHERE last_message_user_id = '375958'