phpmyadmin Question posted in
The official documentation can be found here.

How To Delete 200k Data With Specific User ID? – Phpmyadmin

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

Tags:

3

Answers


  1. 0

    Its your IN clause that has limit, check the max_allowed_packet

    you can do this query instead.

    delete from User_data where user_ID <= 200000

    or 

    delete from User_data where user_ID order by id desc limit 200000

    or (at least this one you have a range)

    delete from User_data where user_id between 18852 and 18853
or user_id between 22233 and 22238
or user_id between 555551 and 555553
    Login or Signup to reply.
  2. 0

    You can try this

    DELETE FROM User_data WHERE user_ID LIMIT 200000;
    Login or Signup to reply.
  3. 0

    You could try populating a temporary table containing the user_id values to be deleted, and then do a delete join:

    CREATE TEMPORARY TABLE user_ids (user_id INT PRIMARY KEY);
INSERT INTO user_ids (user_id)
VALUES
    (18852), (22233), (31257888), (234414), (555551), ..., (88884442);
-- or maybe load from CSV using LOAD DATA

DELETE u1
FROM User_data u1
INNER JOIN user_ids u2
    ON u1.user_id = u2.user_id;

    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 current WHERE IN). Also, this solves the problem of trying to pass in too many IN parameters in the query. With this solution, you don’t have to do that at all.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search