HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

mysql takes too long to just add a column

Submitted by: @import:stackexchange-dba··
0
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?

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: 0


As 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_bin


MySQL 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

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/INPLACE

Code 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/INPLACE

Context

StackExchange Database Administrators Q#319875, answer score: 2

Revisions (0)

No revisions yet.