MySQL. You figure out what exactly to delete and I'll let you:
Database Schema--FCOD
MySQL. Good. That's why I was suprised when you said it wasn't easy. While the Wiki software might not make it easy, SQL does.
First of all, before doing a mass delete on a database, back it up. You can always revert to the backup if you screw up. That being said....
It's actually fairly easy to delete (there are some gotchas, but not many), assuming that the Schema has "ON DELETE CASCADE" for every table that uses the "user_id" field (which is Primary Key for "user" table) as a foreign key. (If it doesn't, then the database management system will bitch that you have to delete all corresponding foreign key table entries first before you can delete the entry with the primary key. And based on the schema picture you showed, if they don't have cascading setup, they should be drug out into the street and shot.
For starters, I'm pretty sure none of our legitimate users start their names with a numeric character. So you could remove all of those with the statment:
DELETE user WHERE user_name LIKE '[0-9]%'
This one statement will knockout 582 of our SPAM accounts. Sound good at first, but....
Now things get a bit hairy. You're left looking for all valid accounts and remove ranges of things that don't include the valid account strings. So you'd basically have to use wildcards like above to delete the bogus users multiple times. This is a real pain.
So a better way would be to look through the list and write down all the users you want to keep. Then use the following statement:
DELETE user WHERE user_name <> 'user1' AND user_name <> 'user2' AND user_name <> 'user3'....
Where each string in single quotes is the name of a user you want to keep. This is probably your best bet, but it takes a bit of work on your part.
Now, there is a quick and dirty way, but you run the risk of throwing out some baby with the bathwater. If you notice, we have a category of "Players" and we also have a "user_groups" table which has a Foreign Key to the user IDs. Not knowing what user groups the database contains, I can't tell you who belongs to what group. If the active users belong to some group and the spam users don't, you can do something like this:
DELETE user
WHERE user_id NOT IN (SELECT ug_user FROM user_groups)
You can add a " WHERE ug_group = 'group name' " to the end of the SELECT if you know what groups you want.
Another quick way is to remove all users who don't have their pages assigned to the "Players" category. I think all legitimate users that have created pages are in the "Players" category. However, this is a SQL statement that involves 4 tables so it's a bit complex. Also, any legitimate logins that don't have a user page at all will be deleted and will have to reregister. But this way involves probably the least amount of physical work on your part.
Personally, I'd just use my second suggestion where you create a list of users you want to keep and you delete everyone who isn't them. There can't be more than 50 or so.