1

I know this has been asked in the past, but sometimes things change over time and I wanted to double check.

I have a table with about 9 Billion rows. Should I add the index before inserting the data or after. I'm using Aurora. Does it matter if I'm adding more than one index?

Everything, I'm aware of says you should do this after the insert, but one of my colleagues is insisting it's faster to do it on the insert.

  • For a table that is bigger than RAM, it is generally faster to do a sort-merge on disk. (I can't address your specific case.) Benchmark it and let us know what you find out. – Rick James Oct 15 at 0:11
  • You are saying that you have 9B rows ready to insert into a table, not that you will be gradually adding 9B rows? – Rick James Oct 15 at 0:12
  • Correct. I'm copying them from one DB to another. – John Oct 17 at 15:11
0

Have the primary key in place, but not the secondary keys. When finished loading, add all the secondary keys in a single ALTER statement.

Caveats:

  • I have not done that particular task, so I dont speak from experience.
  • There have been changes over the years in how ALTER works. Doing multiple alters at once used to be optimal. But with online/instant/etc ALTERs, it is hard to say. Still, the current ALTER .. ADD INDEX.., even though it comes back instantly, has a lot of work to do afterwards.
  • If the PRIMARY KEY in not in place to start with, it will have to reorganize the data. However, this aspect may depend on whether the source data is in PK-order.
  • Hi Rick, Can you explain why you think that's the best approach? – John Oct 17 at 16:23
  • What I gave you is my best guess. I added caveats. – Rick James Oct 17 at 21:12

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.