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 .

  • How many drives are supporting the 2TB table? – Michael Kutz May 23 '20 at 15:25
  • @MichaelKutz sorry i did not get how many drivers ? – Atharv Thakur May 23 '20 at 16:13
  • How many physical drives are in your system? What is the RAID configuration? What is the sustainable throughput of your disk subsystem? How fast can you copy 2TB of data? Does your expected CREATE INDEX time coincide with what your system can handle? Did you run benchmarks on the I/O subsystem before hand? If so, what are the results? – Michael Kutz May 23 '20 at 16:22
  • 1
    Additional information request. Any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional very helpful information, if available includes - ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions for reducing index creation time required. – Wilson Hauck May 25 '20 at 19:42
  • Is that 9 single-column indexes? That is rarely optimal. Let's see the queries that need them; there may be some 'composite' indexes that will help even more. – Rick James May 31 '20 at 2:30

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

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

That takes also long, but should be faster

  • Does partitioning will help ? – Atharv Thakur May 23 '20 at 16:14
  • @AtharvThakur No, partitions it will only complicate your life. – Wilson Hauck May 25 '20 at 19:34
  • INDEX(a,b) versus INDEX(a), INDEX(b) -- These are not equivalent. See mysql.rjweb.org/doc.php/index1 – Rick James May 31 '20 at 2:40
  • With 2 or more columns, the overwhelming majority of SO prefer combined index when they are used together – nbk May 31 '20 at 7:31

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.