Delete thousands of members from an EE2 site

Question

I had over 40 000 spam member accoutns created and need to delete them.

I tries adding &group_id=5&perpage=1000 to my view all members page which works fine, but as soon as I click a column to sort them by ID or last visit it defaults back to 20 members.

How can I easily display and delete 40000 membrs accounts?

This is NOT using EE1.6 but EE2, so NOT a duplicate question. PLUS

I cannot just delete the entire member group. The spam member accounts are spread out through the group. I will say again if I display 1000 per page and try and edit the order (ie: sort by last visit or join date to help group the spam accounts onto one page for easy deletion, it defaults back to 20 members per page)

Answer 1

If you have any visible pattern for spam accounts, you can delete them just with query. My examples:

#Delete all pending ExpressionEngine members mysql query
DELETE FROM exp_member_data where member_id in (select member_id FROM exp_members where group_id = 4) LIMIT 1000000
DELETE FROM exp_message_folders where member_id in (select member_id FROM exp_members where group_id = 4) LIMIT 1000000
DELETE FROM exp_member_homepage where member_id in (select member_id FROM exp_members where group_id = 4) LIMIT 1000000
DELETE FROM exp_members where group_id = 4 LIMIT 1000000

#remove all users who have links in bio (be carefully with this!)
DELETE FROM exp_member_data where member_id in (select member_id FROM exp_members where bio REGEXP 'http') LIMIT 1000000
DELETE FROM exp_message_folders where member_id in (select member_id FROM exp_members where bio REGEXP 'http') LIMIT 1000000
DELETE FROM exp_member_homepage where member_id in (select member_id FROM exp_members where bio REGEXP 'http') LIMIT 1000000
DELETE FROM exp_members where bio REGEXP 'http' LIMIT 1000000


#more patterns for select spammers (you will also need do the same for tables exp_member_data, exp_member_homepage and exp_message_folders ) : 
SELECT member_id FROM exp_members WHERE email REGEXP '[.].*[.].*[.].*[.].*@'; #select members who have emails with 4 dot's in name

select member_id FROM exp_members where bio REGEXP 'url=www.'
select member_id FROM exp_members where bio REGEXP 'a href="'

#most common spammers emails
select member_id FROM exp_members WHERE email REGEXP '.*@21cn.com' OR email REGEXP '.*@163.com' OR email REGEXP '.*@qq.com' OR  email REGEXP '.*@tom.com' OR email REGEXP '.*@mailnesia.com'

Using this method in combination with last check using StopSpammers DB check, it's take for me about 1h to cleanup DB with 120k members to 15'000 accounts which looked more real.