I want to delete a table with 10,000,000 records. Due to some restrictions I can't do truncate on that table (I don't have the DROP privilege), I can think of two options:


  2. To set some table level logging off so that mysql doesn't put data in rollback segments and that could make it faster as it happens in oracle.

Please throw some light on it while keeping in mind that I can't use TRUNCATE.

  • Are there foreign key constraints, referencing the table? Perhaps you can DROP and (re)CREATE. Jul 12, 2013 at 10:49
  • I can't drop the table, in that case i would have used TRUNCATE which does the same thing internally, but unfortunately that's not an option. Jul 12, 2013 at 10:50
  • Why can't you do that? Jul 12, 2013 at 10:51
  • because i dont have drop privilege, i have insert update delete privileges only.. Jul 12, 2013 at 10:53
  • What is the engine of table ? Jul 12, 2013 at 11:00

1 Answer 1


If you don't have privileges to DROP table you should DELETE in chunks.

Use MySQL Limit clause to delete in chunks.

  • thanks for the suggestion of chunk delete but i am more looking towards option like QUICK DELETE or to set logging off for that particular session. When i say logging i mean to say rollback logging. Jul 12, 2013 at 11:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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