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

mysql large table delete – Phpmyadmin

i am using a mysql database version 5. I have a table, that has about 500.000.000 rows.The table reclaims 50GB of diskspace if i trust phpmyadmin. I like to delete 90% of the rows to reclaim disk space.

What is the best option?

  • I can delete the rows using a script and run "OPTIMIZE TABLE". The table will be locked but i dont know how long it can take? Have anybody some experiences?

  • I can drop the table, create a new one and import the 10% manually. How long drop table will take at this size?

Thanks!

Tags:

2

Answers


  1. 0

    Since you want to delete most of the rows in the table, I would recommend moving the rows you want to keep to a temporary table, truncating the table, then re-importing. This still requires downtime on the table (that is, there should be no reads or writes to the table while the operation is in progress), but should be an order of magnitude faster than deleting row by row.

    create table tmptable as select * from mytable where ...;
truncate table mytable;
insert into mytable select * from tmptable;
drop table tmptable;

    Implictely, truncate table drops and recreates the table, which will reclaim the space. The documentation has a few interesting bullet point as regard to your question, such as:

    Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.

    Login or Signup to reply.
  2. 0

    the fastest Way is

    1. to copy the wanted files into a temproary table.
    2. TRUNCATe THE TABLE
    3. copy them back from the temporary table
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search