A long over due update to this thread. The original MySQL queries would delete a lot of valid users with valid posts. Here is a refined Query that will delete only users that have no posts, have not validated their email, have not accepted the board rules, are over 50 days old and have no private messages.

The reason for all the checks for no this and no that, are from members that signed up back in the early days of this forum when a lot of features didn't exist. Our board dates back to 2000.

Here is the query to find bogus users:

Code
SELECT USER_ID 
FROM ubbt_USERS 
WHERE USER_ID in (
SELECT u.USER_ID FROM ubbt_USERS as u
JOIN ubbt_USER_DATA AS ud 
ON ud.USER_ID = u.USER_ID  
JOIN ubbt_USER_PROFILE as uup 
ON u.USER_ID=uup.USER_ID  
LEFT JOIN ubbt_PRIVATE_MESSAGE_USERS AS upmu 
ON upmu.USER_ID = u.USER_ID 
LEFT JOIN ubbt_PRIVATE_MESSAGE_TOPICS AS upmt 
ON upmt.TOPIC_ID = upmu.TOPIC_ID 
AND upmt.USER_ID = u.USER_ID 
LEFT JOIN ubbt_PRIVATE_MESSAGE_POSTS AS upmp 
ON upmp.TOPIC_ID = upmu.TOPIC_ID 
AND upmp.USER_ID = u.USER_ID 
WHERE USER_TOTAL_POSTS=0  
AND upmu.USER_ID IS NULL 
AND upmt.USER_ID IS NULL 
AND upmu.USER_ID IS NULL 
AND USER_LAST_VISIT_TIME < unix_timestamp(now()) - (60*60*24*50)  
AND USER_REGISTERED_ON=USER_LAST_VISIT_TIME
);

And the query to delete those users:
Code
DELETE u, ud, uup 
FROM ubbt_USERS as u 
JOIN ubbt_USER_DATA AS ud 
ON ud.USER_ID = u.USER_ID  
JOIN ubbt_USER_PROFILE as uup 
ON u.USER_ID=uup.USER_ID  
LEFT JOIN ubbt_PRIVATE_MESSAGE_USERS AS upmu 
ON upmu.USER_ID = u.USER_ID 
LEFT JOIN ubbt_PRIVATE_MESSAGE_TOPICS AS upmt 
ON upmt.TOPIC_ID = upmu.TOPIC_ID 
AND upmt.USER_ID = u.USER_ID 
LEFT JOIN ubbt_PRIVATE_MESSAGE_POSTS AS upmp 
ON upmp.TOPIC_ID = upmu.TOPIC_ID 
AND upmp.USER_ID = u.USER_ID 
WHERE USER_TOTAL_POSTS=0  
AND upmu.USER_ID IS NULL 
AND upmt.USER_ID IS NULL 
AND upmu.USER_ID IS NULL 
AND USER_LAST_VISIT_TIME < unix_timestamp(now()) - (60*60*24*50)  
AND USER_REGISTERED_ON=USER_LAST_VISIT_TIME;