If your WordPress site has been around for a while without proper spam protection on the registration form, you may have woken up one day to find hundreds of thousands of fake subscriber accounts clogging your database. This happened to us — 524,000 spam subscribers on a Plesk-hosted WordPress site, enough to make the site completely unresponsive and break the Plesk WordPress Toolkit entirely.
This post walks through how we handled it using a direct MySQL bash script — no plugins, no PHP timeouts, no drama.
Why Not Just Use a Plugin?
Our first instinct was to reach for a plugin like Bulk Delete or Delete Inactive Users. These work fine for a few thousand users, but at 500k+ rows, they will:
-
Time out before finishing
-
Exhaust PHP memory limits
-
Make an already struggling site even slower
-
Require multiple manual runs with no progress tracking
For a database this size, going directly to MySQL is the only practical option.
Prerequisites
-
SSH access to your server
-
MySQL credentials (check
wp-config.php) -
Your WordPress table prefix (found in
wp-config.phpas$table_prefix) -
A database backup (non-negotiable — do this first)
Step 1 — Back Up Your Database First
wp db export ~/backup-$(date +%F).sql --add-drop-table && echo "Backup complete"
Or via mysqldump directly:
mysqldump -u YOUR_DB_USER -p YOUR_DB_NAME > ~/backup-$(date +%F).sql
Do not skip this. You are about to delete hundreds of thousands of rows.
Step 2 — Check Your Subscriber Count
Before running anything destructive, confirm how many subscribers you are actually dealing with:
SELECT COUNT(*) FROM YOUR_PREFIX_users u
INNER JOIN YOUR_PREFIX_usermeta um ON u.ID = um.user_id
WHERE um.meta_key = 'YOUR_PREFIX_capabilities'
AND um.meta_value LIKE '%subscriber%'
AND u.ID NOT IN (SELECT DISTINCT post_author FROM YOUR_PREFIX_posts);
Replace YOUR_PREFIX_ with your actual table prefix (e.g. wp_, oYT6qL_, etc.).
Step 3 — The Bulk Delete Script
This script deletes subscriber accounts that have authored zero posts, in configurable batches, with a real-time count of remaining rows every 5 batches.
#!/bin/bash
# -----------------------------------------------
# WordPress Spam User Cleanup Script
# Deletes subscriber accounts with no posts
# -----------------------------------------------
# Database credentials — update these
DB_USER="your_db_user"
DB_PASS="your_db_password"
DB_NAME="your_db_name"
DB_HOST="localhost"
DB_PORT="3306"
DB_PREFIX="wp_" # Change to your actual prefix
# Tuning
S_LIMIT=10000 # Rows to delete per batch
BATCH=0
# Reusable MySQL connection command
MYSQL_CMD="mysql -u $DB_USER -p$DB_PASS -h $DB_HOST -P $DB_PORT $DB_NAME"
while true; do
echo "Running delete batch $((BATCH+1))..."
$MYSQL_CMD -e "
DELETE u, um FROM ${DB_PREFIX}users u
INNER JOIN ${DB_PREFIX}usermeta um ON u.ID = um.user_id
WHERE u.ID IN (
SELECT ID FROM (
SELECT u2.ID FROM ${DB_PREFIX}users u2
INNER JOIN ${DB_PREFIX}usermeta um2 ON u2.ID = um2.user_id
WHERE um2.meta_key = '${DB_PREFIX}capabilities'
AND um2.meta_value LIKE '%subscriber%'
AND u2.ID NOT IN (SELECT DISTINCT post_author FROM ${DB_PREFIX}posts)
LIMIT $S_LIMIT
) tmp
);
"
(( BATCH++ ))
# Recount every 5 batches to avoid hammering the DB on every iteration
if (( BATCH % 5 == 0 )); then
REMAINING=$($MYSQL_CMD -se "
SELECT COUNT(*) FROM ${DB_PREFIX}users u
INNER JOIN ${DB_PREFIX}usermeta um ON u.ID = um.user_id
WHERE um.meta_key = '${DB_PREFIX}capabilities'
AND um.meta_value LIKE '%subscriber%'
AND u.ID NOT IN (SELECT DISTINCT post_author FROM ${DB_PREFIX}posts);
")
echo "--- Batches done: $BATCH | Remaining: $REMAINING ---"
[ "$REMAINING" -eq 0 ] && echo "All done!" && break
else
echo "Batch $BATCH done, continuing..."
fi
sleep 1
done
# -----------------------------------------------
# Cleanup: remove orphaned usermeta + analyze
# -----------------------------------------------
echo "Running cleanup..."
$MYSQL_CMD -e "
DELETE FROM ${DB_PREFIX}usermeta
WHERE user_id NOT IN (SELECT ID FROM ${DB_PREFIX}users);
"
$MYSQL_CMD -e "
ANALYZE TABLE ${DB_PREFIX}users, ${DB_PREFIX}usermeta;
"
echo "Cleanup complete!"
Save this as cleanup-spam-users.sh, update the credentials and prefix at the top, then run:
chmod +x cleanup-spam-users.sh
bash cleanup-spam-users.sh
How It Works
Batched deletes — rather than attempting to delete 500k rows in a single query (which will lock your tables and likely time out), the script deletes 10,000 rows at a time with a 1-second pause between batches. You can tune S_LIMIT up or down depending on your server load.
Joined delete — the query deletes from both wp_users and wp_usermeta in a single statement using a JOIN, so you are never left with orphaned metadata from deleted users.
Real count checks — instead of tracking progress with arithmetic (which goes negative when the final batch has fewer rows than the limit), the script queries the actual remaining row count from the database every 5 batches. This is accurate and exits cleanly at zero.
Subquery wrapping — MySQL does not allow DELETE and SELECT from the same table directly. The inner query is wrapped in a derived table alias (tmp) to work around this limitation.
Cleanup pass — after the main loop, any orphaned usermeta rows left over from previous partial cleanups are removed, and ANALYZE TABLE is run to update index statistics.
Notes on the OPTIMIZE TABLE Warning
If you run OPTIMIZE TABLE on InnoDB tables, you will see:
Table does not support optimize, doing recreate + analyze instead | OK
This is not an error. InnoDB does not support traditional optimize operations, so MySQL automatically performs a full table rebuild and analyze instead. The OK status at the end means it completed successfully.
After the Cleanup — Prevent It Happening Again
Once your database is clean, lock down registration to stop the same thing happening again:
-
Disable open registration if you do not need it:
Settings → General → uncheck "Anyone can register" -
Add CAPTCHA to the registration form — Cloudflare Turnstile is invisible and free
-
Block disposable email domains with a plugin like WP Disposable Email Blocker
-
Enable Cloudflare Bot Fight Mode (free tier) to block bot traffic before it hits WordPress
-
Consider CleanTalk (~$12/year) for fully automated spam registration blocking with no CAPTCHA friction
Results
After running the script against our 524k spam subscriber database, the site went from completely unresponsive to loading normally. The Plesk WordPress Toolkit was also able to reconnect once the database load dropped.
Total time to delete 524,000 rows in batches of 10,000: approximately 20–30 minutes running unattended over SSH.
Hope this helps anyone else who has found themselves in the same situation. Happy to answer questions in the comments.