Fast way of delete MySQL record

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?

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.

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

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.

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.

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