Mass delete of welcome PM from all users

[annatar]

Hello all.

I've a situation where there are close to 20k users who have their original welcome PM message left in their inbox and no other PM. I suspect that this is causing the maintenance script which scans the PM table to take longer than it should and as a result it is sometimes slowing down the forum.

I want to perform a mass purge of all of those messages from the database. I wrote two simple SQL statements which I used on a test copy of the forum database and didn't notice any errors. Those statements are:

delete from `forumPrefix_pmtext` where message like 'Thank you for registering%'
delete from `forumPrefix_pm` WHERE pmtextid NOT IN (select `pmtextid` from `forumPrefix_pmtext`)

When running those on the test database it seems to have deleted everything I wanted and the test forum seems to behave without issue.

Does anyone know of any reason why performing those statements would be bad? I didn't see any explicit database associations which would be broken by performing the operations directly with SQL.

I know vBulletin has the option to list all users with only 1 PM via the admincp but honestly it isn't realistic to use those drop down boxes to delete PMs for 20k users.

[Marco van Herwaarden]

You would only need to delete them from the 'pm' table. The pmtext table is cleaned automatically.