patternsqlMinor
Fastest way to change a 600GB table indexed key datatype from INT to BIGINT
Viewed 0 times
datatypeway600gbindexedfastestbigintfromintchangetable
Problem
I need to change a datatype from INT to BIGINT in a 600GB MySQL table. The column has a unique index. I might be good with unsigned INT, but I assume changing to that or BIGINT will be pretty much same pain. The table's engine is InnoDB. What would be easier:
UPDATE:
As requested, MySQL ver 5.5.15, no foreign keys and create table:
- ALTER TABLE
- Copying structure and
INSERT INTO (SELECT *)
- Dumping table and changing dump file table definitions
- Anything else?
UPDATE:
As requested, MySQL ver 5.5.15, no foreign keys and create table:
CREATE TABLE `tbl` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`created_at` datetime NOT NULL,
`tid` bigint(20) NOT NULL,
`t` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`f` tinyint(1) NOT NULL,
`i_id` bigint(20) NOT NULL,
`ir_id` int(11) NOT NULL,
`r_c` int(11) NOT NULL,
`r` tinyint(1) NOT NULL,
`e` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`t` varchar(5) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `user` (`user_id`,`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=1657146169 DEFAULT CHARSET=utf8Solution
Assuming your table does not have any triggers on it, you should consider using
It will still take a fair amount of time given the size of the table.
Also, with this method or with
pt-online-schema-change since it will allow you to ALTER the table without locking it.It will still take a fair amount of time given the size of the table.
Also, with this method or with
ALTER TABLE you will need to make sure you have 600 GB of extra disk space to support two copies of the table while it is being rebuilt.Context
StackExchange Database Administrators Q#52056, answer score: 2
Revisions (0)
No revisions yet.