We’re trying to alter the primary key on a large table to be more efficient with Innodb (we’ve noticed a significant drop in performance on the table since migrating from myisam and we’re hypothesizing that the pk is a large factor).
The current primary key is a 2-column pair - a randomly generated varhchar(16) “id” and an integer “version”. We’d like to replace this with an auto-increment integer and make (id,version)a unique index instead.
The other columns of the table are all longtext or longblob (full schema below). There are ~300k records, totaling ~20GB.
We’ve been experimenting with different approaches on our test environment to try and find the fastest way to make the change, but it’s going a lot slower than we’d like (ideally we’d like to make the change on our production db with max 2 hours downtime).
So far we’ve tried two approaches:
- Change existing table
- drop pk,
- alter table add new auto_inc pk
- add unique index
- create new table and copy records
- create new table with auto_inc pk, no indexes
- INSERT (…) INTO newTable SELECT (…) from oldTable
- add unique index
- rename tables
The first approach took 5 hours, 3 of which just to drop the primary key. We’re running the second test now, but it’s already taken over 1.5 hours and hasn’t finished copying the rows over.
Can anyone suggest any other ways which might be faster? Is 2 hours just completely unrealistic?
schema (columns renamed):
id varchar(16) collate utf8_unicode_ci NOT NULL,
version int(11) NOT NULL,
col_1 longblob NOT NULL,
col_2 longblob NOT NULL,
col_3 longblob NOT NULL,
col_4 longblob NOT NULL,
col_5 longblob NOT NULL,
col_6 longblob NOT NULL,
col_7 longblob NOT NULL,
col_8 longblob NOT NULL,
col_9 longblob NOT NULL,
col_10 longtext collate utf8_unicode_ci NOT NULL,
col_11 longtext collate utf8_unicode_ci NOT NULL,
col_12 longtext collate utf8_unicode_ci NOT NULL,
col_13 longblob NOT NULL,
col_14 longblob NOT NULL,
col_15 longblob NOT NULL,
col_16 longblob NOT NULL,
col_17 text collate utf8_unicode_ci NOT NULL,
col_18 longblob NOT NULL, PRIMARY KEY (