Re: delete caused all kinds of problems

[Ingo Strüwing]

Do you have many indexes on the table?
You may want to ALTER TABLE ... DISABLE KEYS; DELETE ...; ALTER TABLE ENABLE KEYS;
This will however grow your index file with every ENABLE. You will need to OPTIMIZE TABLE before it becomes too big (Bug #4692).

Another way could be CREATE TABLE new_table (...) AS SELECT ... WHERE id>=500000; RENAME TABLE ... old_table; RENAME TABLE new_table ...; DROP TABLE old_table.