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 deleted millions of rows from one of the table with innodb engine. I know that I need to run optimize table to recover the space. But running that will cause lock on the table, which will affect the production queries. Is there any other way to recover the space without affecting the production queries.

share|improve this question
    
Are you using innodb_file_per_table? If you don't know, what is the output of SHOW GLOBAL VARIABLES LIKE 'innodb_file_per_table';? –  RolandoMySQLDBA Aug 26 '13 at 16:07
    
How many rows does the table have now ??? –  RolandoMySQLDBA Aug 26 '13 at 16:38
    
@RolandoMySQLDBA: Yes, innodb_file_per_table is ON and there are about 15221827 entries in the table now. –  pradeepchhetri Aug 26 '13 at 18:03
    
Does the table have constraints or foreign keys ??? What is the table's name ??? –  RolandoMySQLDBA Aug 26 '13 at 18:20
    
No there is no constraints or foreign keys. –  pradeepchhetri Aug 26 '13 at 18:36

1 Answer 1

up vote 3 down vote accepted

You will have to take the table offline to prevent any reads or writes that would get locked and probably have to roll back. If the table is called mydb.mytable, here is what you must do:

USE mydb
DROP TABLE IF EXISTS mytablenew;
CREATE TABLE mytablenew LIKE mytable;
ALTER TABLE mytable RENAME mytableold;
INSERT INTO mytablenew SELECT * FROM mytableold;
ANALYZE TABLE mytablenew;
ALTER TABLE mytablenew RENAME mytable;
DROP TABLE mytableold;

Any queries attempting to read from or write to mytable will fail immediately rather than hang on a lock from OPTIMIZE TABLE.

Give it a Try !!!

UPDATE 2013-08-26 15:24 EDT

Look back at the code I suggested. Please note the line

ALTER TABLE mytable RENAME mytableold;

That line takes the table offline with respect to the application.

To get a good idea how long it should take to optimize run the following:

USE mydb
CREATE TABLE mytablenew LIKE mytable;
INSERT INTO mytablenew SELECT * FROM mytable;

However long this takes, is how long it will actually take to run.

share|improve this answer
    
Thank you for your quick reply. I have one question. How much time will it take for a table containing around 15221827 entries. –  pradeepchhetri Aug 26 '13 at 19:14
    
I updated my answer with a way to find out how long... –  RolandoMySQLDBA Aug 26 '13 at 19:24
    
Thank you very much !! –  pradeepchhetri Aug 26 '13 at 19:38

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.