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: