db migration 212

[peterjanssens]

migration 212 seems to take quite some time to run because of the (large) ca_sql_search_word_index.

would it be an option to combine the three last statements so that the recalculations are (perhaps) not 3x ?

ALTER TABLE ca_sql_search_word_index
MODIFY COLUMN word_index mediumint unsigned not null default 0,
MODIFY COLUMN word_count mediumint unsigned not null default 0,
MODIFY COLUMN field_index mediumint unsigned not null default 0;

would it be faster to:
a) drop the (table) indexes (i_....) before, and rebuilding them after the alters in that migration script,
... DROP INDEX i_row_id;
...
ALTER TABLE MODIFY COLUMN...
... ADD INDEX i_row_id
...

b) or dropping(truncating) the entire search tables and rebuild them after the sql/migration with rebuild-search utils ?

(option b is not as set and forget as option a, that is more verbose and maybe prone to legacy migration issues ??)

[peterjanssens]

reran the migration from a backup and the combined statement did about 45 minutes instead of almost two hours (local installation) - copy to tmp table reduced to one time probably.

(still, truncating and rebuilding the search entirely would remain a viable option in those terms)

[collectiveaccess]

It will be quicker to truncate and recreate in just about all cases. Will make the change.

[collectiveaccess]

d5379b1

[yelto]

I understand that some installs might need larger ints as indices, especially if they don't only index metadata but also data/content, but is it wise to force a full reindex (or equivalent) on all for a 2.0.X release?

[peterjanssens]

The combined ALTER statement is already a big win, reindexing appears to be in the same order of magnitude to that so I would be ok with leaving that option (trunc+reindex after) to the sysadmin in preparation of lengthy migrations (ideally we would run the reindex in background but that might depend on the application setup; not in background would effectively double the 'waiting time' again).

Would it be possible that we provide some of this feedback at the start of the migration (before or during). It remains a bit scary to look at a fairly minimal screen fingers crossed hoping for it to end well. Production systems will be quicker but this one in particular is expensive for all with some data.