Mysql - create new column with index on existing database table with data

We can achieve it by executing below query.

ALTER TABLE `posts`
	ADD COLUMN `category_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '1';

CREATE INDEX index_category_id ON posts ( category_id );

Just change:

• database table - posts
• column name - category_id
• index name - index_category_id

Example

Create database, table and insert data.

CREATE DATABASE `post_tests`;
USE `post_tests`;

CREATE TABLE `posts` (
	`post_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	`post_content` LONGTEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
	PRIMARY KEY (`post_id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;

INSERT INTO `posts` (`post_id`, `post_content`) VALUES (1, 'post 1');
INSERT INTO `posts` (`post_id`, `post_content`) VALUES (2, 'post 2');
INSERT INTO `posts` (`post_id`, `post_content`) VALUES (3, 'post 3');

Screenshot 1:

Screenshot 2:

Query - create new column with index

ALTER TABLE `posts`
	ADD COLUMN `category_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '1';

CREATE INDEX index_category_id ON posts ( category_id );

Our database after execution

Screenshot 3:

Screenshot 4: