Index creation in extremely slow in Mysql Aurora 5.7

Question

I have a table in mysql Aurora 5.7 version .

The db instance has very good configuration 488 GB ram (db.r4.16xlarge).

Data base is static data base no application is running on this .

I have 1.5 Billions record approx 2TB size in the table .

The table does not have any index except primary key .

Table has 35 Columns .

This table does not have any partition

Now i need to create Index on nine other columns and here i hit hard . It takes 5 Hours to create one index and when i run command to create all 9 index at same time it took almost 10 hours and then i cancelled .

Is there any way i can make this index creation faster ? Is creating 10 partition will help creating Index faster ?

Why i need to create Index on 1.5 Billions records is because i have migrated my table from Orcale Source to Mysql using AWS DMS service .

Before migration i need to drop index at target and then have to migrate so that migration will be faster and it is actually very fast .

Without Index migration took 10 hours where as with Index it is taking 25 Hours and some time it fails as well .

Answer 1

You have a lot of rows., every row has to be inserted into the index, that takes time, live with it.

You can try to use only one index for all columns

CREATE INDEX name 
ON yourtable(column1,column2,column3,column4...colmn9);

That takes also long, but should be faster

Answer 2

Partitioning rarely helps with performance. http://mysql.rjweb.org/doc.php/partitionmaint

Adding an index to a partitioned table probably takes longer than to the equivalent non-partitioned table -- it will block access throughout the action.

There have been several important changes to Oracle's MySQL; Aurora may not have picked them up yet. (I don't know how close Aurora 5.7 is to MySQL 5.7.) You tagged the question [mysql-5.6]; which is it?

Let's see the actual indexes you what to add; there may be some tricks and/or cautions to discuss.

In general, it is not useful to index "flags" or other low-cardinality columns. That is, if they are alone in the index.

Prefixing is rarely useful. Example: INDEX(foo(10))

Percona Toolkit may have something to help.

Let's see the queries that need them; there may be some 'composite' indexes that will help even more. Also SHOW CREATE TABLE.