Sign up ×
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 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.

share|improve this question
Are there foreign key constraints, referencing the table? Perhaps you can DROP and (re)CREATE. –  ypercube Jul 12 '13 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. –  Hitesh Chouhan Jul 12 '13 at 10:50
Why can't you do that? –  ypercube Jul 12 '13 at 10:51
because i dont have drop privilege, i have insert update delete privileges only.. –  Hitesh Chouhan Jul 12 '13 at 10:53
What is the engine of table ? –  Abdul Manaf Jul 12 '13 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.

share|improve this answer
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. –  Hitesh Chouhan Jul 12 '13 at 11:08

Your Answer


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.