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 .
Best Answer
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
That takes also long, but should be faster