Last days I am testing and doing benchmarks in order to find out how long it takes to upgrade Magento DB when we upgrade between major Magento versions.
I am testing kind of large DB. This DB contains only vital data and all unnecessary tables are truncated (log tables, import batches and etc.)
Where is the bottle neck?
The catalog data is not large but sales history tables are gigantic. I did couple of dry runs and noticed that adding / dropping indexes, adding / removing columns take ages. Basically I observed what MySql was doing … it was creating temporary tables like crazy (this obviously increased time for upgrade)
Plan of attack:
I see a possibility how to save some time but it needs preparation.
- Use DB version in which large sales tables are empty.
- Run Magento upgrade.
- Import the data in sales tables.
- Run some data-upgrade scripts again because new column were created during upgrade and I have to fill them with values.
My hope is that with step (3) I could reduce time which was wasted of creating temporary table every time when indexes was added / dropped and column was added / dropped.
What the community thinks about this approach? Do you see any risk for data inconsistency when the shop goes live after upgrade?