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 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:

  1. DELETE QUICK

  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
show 1 more comment

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.

DELETE FROM TABLE_NAME LIMIT 10000;
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
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.