1

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.

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

1 Answer 1

Reset to default
2

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;
Improve this answer
1
  • 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, 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.