Create a MySQL index on already existing table with a lot of data

Question

I have a Spring Boot service with a MySQL database (AWS RDS).

There is a specific table, that contains around 2 millions of rows, and some queries on it make the CPU go up to the database instance.

I noticed that there isn't an index on the used column so I would like to try to add this index.

The questions are:

• Can I add (without any problems) this index on this table that already contains a lot of rows? I'm using Flyway to manage the db migrations.
• The specific column contains strings, are there some other index configurations that It's better to use for this scenario?

Some additional infos:

• MySQL version is 5.7.33;

• The table, at the moment, does not contain any other relationships;

• The table is very simple and it's reported below:

  CREATE TABLE IF NOT EXISTS info(

  field_1 varchar(36) NOT NULL,

  field_2 text DEFAULT NULL,

  my_key varchar(36) DEFAULT NULL,

  field_3 varchar( 255) DEFAULT NULL,

  field_4 varchar(10) DEFAULT NULL,

  field_4 varchar(10) DEFAULT NULL,

  field_6 varchar(10) DEFAULT NULL,

  field_7 varchar(36)NOT NULL,

  creation_date datetime DEFAULT NULL,

  modification_date datetime DEFAULT NULL,

  PRIMARY KEY (field_1)

  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• The table contains now around 2 millions of rows;

• The query is something like:

  SELECT * FROM info WHERE my_key = "xxxx"

  and it will be executed a lot of times

The idea is to create this index:

CREATE INDEX my_key ON info (my_key);

Answer 1

With the more recent versions of MySql you can create an index without locking the table:

The table remains available for read and write operations while the index is being created. The CREATE INDEX statement only finishes after all transactions that are accessing the table are completed, so that the initial state of the index reflects the most recent contents of the table.

Obviously creating an index is an extra effort for the database so if your database is in suffering state try to update the index when you have a decrease of the activities performed on the db.