Cleaning Up Bot Registrations in WooCommerce
…or Optimizing Slow Sub-Queries in WordPress
Bot registrations are a nuisance in many WooCommerce sites. Cleaning them up seems to be a trivial task: just delete all users without a placed order from a month ago and backwards.
select * from wp_users where user_registered < "2020-07-01 00:00:00"; 47665 rows in set (0.06 sec) select meta_value from wp_postmeta where meta_value = '_customer_user'; 51253 rows in set (0.44 sec)
Okay, so we almost 50 thousand customers and a bit over 50 thousand orders.
The query to delete all the users that have no order is seemingly a simple one:
delete from wp_users where user_registered < "2020-07-01 00:00:00" and id not in (select meta_value from wp_postmeta where meta_key = '_customer_user' group by meta_value);
Great. Yet there's a huge issue: Query OK, (59 min 7.22 sec)
Ooomph! This won't effing do!
