annatar
02-10-2010, 08:14 PM
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.
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.