I’m currently a newbie on Mysql and I’ve been handed a task to actually remove 200,000 plus data through phpmyadmin.
I’ve actually screen through all the data and extracted the user ids from it, just to know that it does not work as how i though it would. By using this code :
DELETE FROM User_data
WHERE user_id IN ( 18852,22233,31257888,234414,555551....88884442);
I was expecting it to remove all 200k data at once, but it only deleted 5k data instead.
Thanks For Reading this! Hope for some replies
Edited : The User_ids are not ordered properly
3
Answers
Its your IN clause that has limit, check the max_allowed_packet
you can do this query instead.
or
or (at least this one you have a range)
You can try this
You could try populating a temporary table containing the
user_id
values to be deleted, and then do a delete join:Now, because the
user_id
column in the temporary table is a primary key, the join lookup should be very fast (probably a bit faster than your currentWHERE IN
). Also, this solves the problem of trying to pass in too manyIN
parameters in the query. With this solution, you don’t have to do that at all.