Continuing Table refactoring & application version upgrades, Part I, we now discuss code & database upgrades which require DROP operations. As before, we break apart the upgrade process into sequential steps, each involving either the application or the database, but not both.
As I’ll show, DROP operations are significantly simpler than creation operations. Interestingly, it’s the same as in life.
DROP COLUMN
A column turns to be redundant, unused. Before it is dropped from the database, we must ensure no one is using it anymore. The steps are:
- App: V1 -> V2. Remove all references to column; make sure no queries use said column.
- DB: V1 -> V2 (possibly failover from M1 to M2), change is DROP COLUMN.
DROP INDEX
A possibly simpler case here. Why would you drop an index? Is it because you found out you never use it anymore? Then all you have to do is just drop it.
Or perhaps you don’t need the functionality the index supports anymore? Then first drop the functionality:
- (optional) App: V1 -> V2. Discard using functionality which relies on index.
- DB: V1 -> V2 (possibly failover from M1 to M2), change is DROP INDEX. Check out InnoDB Plugin here. Continue reading » “Table refactoring & application version upgrades, Part II”