Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a 1.2 million record in a database table( it use index column also)

I want to delete old 1 million record. I try to do it using PhpMyadmin but it take more time.

DELETE FROM `table` WHERE `id` < 1000000

Is there any way to do it fast?

I have another question: if i did this using SSH client in command line, will it be fast?

share|improve this question
add comment

migrated from stackoverflow.com Jun 16 '13 at 12:05

This question came from our site for professional and enthusiast programmers.

5 Answers

Another good way to do this is to copy 0.2 mln records to another table and drop the first table with 1.2 mln.

share|improve this answer
    
My answer dba.stackexchange.com/a/44637/877 is the implementation of your answer. You get a +1 for coming up with the idea first. –  RolandoMySQLDBA Jun 16 '13 at 19:17
    
This will surely fail if there are Foreign key constraints referencing the table. –  ypercube Jun 29 '13 at 8:36
    
@ypercube it will also surely fail if the guy does not have a permission to delete a table, and in a lot of other cases. I was solving the question, based on the provided information, not trying to solve it for all possible scenarios. –  Salvador Dali Jun 29 '13 at 9:29
1  
I have not downvoted you, exactly because there is a case where this will work - and possibly be faster than the OP's way. And because the question was migrated from SO. Here however, in DBA.SE, we prefer more detailed answers (yes, covering different, extreme or not scenarios.) I commented so you can add that in your answer (and if you do, add the permissions issue which is nice, hadn't thought of that.) You will see much more upvotes with a thorough answer than with an 1-liner. –  ypercube Jun 29 '13 at 9:40
add comment

Disable the indexes on the table before deleting, and rebuild them after.

share|improve this answer
add comment

There are several more options.

1) Run your delete statements with a LIMIT clause (Eg 1000) in a loop until the table is empty, if your deleting a lot. This causes the delete statement transaction to do less work per run.

2) If you can partition this date you want to delete ahead of time (eg, time series by timestamp) then you can use DROP PARTITION of newer versions of MySQL to instantly delete a large number of rows.

share|improve this answer
add comment

Looking at the query I am concerned with the number of rows being deleted.

Which is better ?

  • Deleting the 1,000,000 million rows
  • Creating a table with 200,000 rows

Look at Deleting the 1,000,000 million rows

  • It takes a long time
  • Table remains the same size
  • You must run OPTIMIZE TABLE to reclaim the space

Look at Creating a table with 200,000 rows

  • Takes the same time as OPTIMIZE TABLE
  • Steps are faster
  • Space Reclaim is Faster

Here are the Steps to Creating a table with 200,000 rows

If the table is InnoDB

CREATE TABLE mytablenew LIKE mytable;
INSERT INTO mytablenew SELECT * FROM mytable WHERE id >= 1000000;
DROP TABLE mytable;
ALTER TABLE mytablenew RENAME mytable;

If the table is MyISAM

CREATE TABLE mytablenew LIKE mytable;
ALTER TABLE mytablenew DISABLE KEYS;
INSERT INTO mytablenew SELECT * FROM mytable WHERE id >= 1000000;
ALTER TABLE mytablenew ENABLE KEYS;
DROP TABLE mytable;
ALTER TABLE mytablenew RENAME mytable;

Give it a Try !!!

On a side note, doing this with SSH client makes no difference since everything is server-side.

share|improve this answer
add comment

Thank you for your answers. Here is my finding

1) This take long time-"Disable the indexes on the table before deleting, and rebuild them after."

2) best way to create new table with 200000 record

CREATE TABLE mytablenew LIKE mytable;
INSERT INTO mytablenew SELECT * FROM mytable WHERE id >= 1000000;
DROP TABLE mytable;
ALTER TABLE mytablenew RENAME mytable;

in this method, the id ( auto increment) starting from 1000000 in the new table. how can i make ti to start from 1?

thanks

share|improve this answer
    
Answers should be answers and new questions should be new questions. Why do you even care for a few millions missing ids anyway? The INTEGERs go up to billions and the BIGINTs up to quadrillions. –  ypercube Jun 29 '13 at 9:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.