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.

In a project, we need to frequently ALTER the tables to delete or add columns. I wonder if this weakens the database performance in long term. If it has a considerable negative impact, what is the approach to keep the database healthy and efficient?

I was thinking of the following approaches:

  1. OPTIMIZE the tables

  2. CREATE a new database with the final architecture, and INSERT the entire data

The database is mysql(5.5) with InnoDB.

share|improve this question
    
Sounds like you're talking about doing a 1 time clean-up, which wouldn't be a bad idea. Depending on your requirements, some shops will version control the schema, even index definitions. –  Eric Higgins Sep 16 '13 at 21:10
    
Just for fun, why are you doing that? –  Max Vernon Sep 16 '13 at 22:44
    
@MaxVernon I know that it sounds crazy, but it is a live project in which the database structure is about to modified. Unfortunately, due to the rush, we could now extend the test period. –  All Sep 16 '13 at 23:26
    
Be scared. Rushing like that makes for problems. –  Max Vernon Sep 16 '13 at 23:32
    
@MaxVernon the project is in beta mode, not alpha. Changes to the database is not about core columns, but additional columns, usually making FK to other tables. The project is conducted based on end-user feedback. –  All Sep 17 '13 at 3:45
add comment

1 Answer 1

up vote 2 down vote accepted

It's an intuitively-sensible concern that you've raised because it would seem to make sense that frequent DDL operations might leave little bits of messiness behind, but my experience has been that this concern is unfounded with InnoDB.

Prior to MySQL 5.6, the vast majority of ALTER TABLE operations actually rebuild the entire table with the new structure, copy all the data, then swap in the new table and delete the old one... so, I see very little potential for frequent changes to have a negative impact down the road.

You might, though, be well-advised to OPTIMIZE TABLE on tables where you've repeatedly inserted and deleted test data to clean up wasted disk space and rebuild the indexes, but unless your test data has been comparable in size to your production data, even this will disappear in the mix as the tables take on live data.

share|improve this answer
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.