patternsqlMinor
mysql takes too long to just add a column
Viewed 0 times
columnjustlongtoomysqltakesadd
Problem
MySQL took 4.5 minutes to just add a column to a table having 150,000 rows.
Is there anything wrong in it or this is just normal. Is there any other alternative to fasten things?
As requested by Ergest -
MySQL version- 8.0.30, command
CPU is 8 core 16GB RAM at my local and at server it's 1 core 2GB, but time was almost the same both ways.
No foreign keys only a FT index.
Is there anything wrong in it or this is just normal. Is there any other alternative to fasten things?
ALTER TABLE job_posts
-> ADD COLUMN `is_hotlist` VARCHAR(5) NULL AFTER `msg_no`;
Query OK, 149001 rows affected (4 min 32.30 sec)
Records: 149001 Duplicates: 0 Warnings: 0As requested by Ergest -
CREATE TABLE `job_posts` (
`job_id` bigint NOT NULL AUTO_INCREMENT,
`title` text COLLATE utf8mb4_bin,
`description` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci,
`state` bigint DEFAULT NULL,
`country` bigint DEFAULT NULL,
`email` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
`city` bigint DEFAULT NULL,
`post_date` datetime DEFAULT CURRENT_TIMESTAMP,
`msg_no` bigint DEFAULT NULL,
`is_hotlist` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`job_id`),
FULLTEXT KEY `full_text` (`description`)
) ENGINE=InnoDB AUTO_INCREMENT=157538 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_binMySQL version- 8.0.30, command
ALTER TABLE job_posts ADD COLUMN cviews BIGINT NULL; almost took 5 minutes.CPU is 8 core 16GB RAM at my local and at server it's 1 core 2GB, but time was almost the same both ways.
No foreign keys only a FT index.
Solution
It might work with algorithm instant as that is the default anyways post 8.0.12
but it will not work for my case as algorithm instant does not work with tables having a full-text index as described here. It shows the error -
ALTER TABLE job_posts ADD COLUMN `is_deleted` int NULL, ALGORITHM = INSTANT;but it will not work for my case as algorithm instant does not work with tables having a full-text index as described here. It shows the error -
Error Code: 1846. ALGORITHM=INSTANT is not supported. Reason: InnoDB presently supports one
FULLTEXT index creation at a time. Try ALGORITHM=COPY/INPLACECode Snippets
ALTER TABLE job_posts ADD COLUMN `is_deleted` int NULL, ALGORITHM = INSTANT;Error Code: 1846. ALGORITHM=INSTANT is not supported. Reason: InnoDB presently supports one
FULLTEXT index creation at a time. Try ALGORITHM=COPY/INPLACEContext
StackExchange Database Administrators Q#319875, answer score: 2
Revisions (0)
No revisions yet.