0

I have a very large table (600M+ records, 260G of data on disk) within MySQL that I need to add indexes to. I'd expected to add them directly, but doing some searching and some recommend creating a placeholder table, creating index(es) on it, dumping from first table and then loading to second table.

Can anyone advise on a preferred approach and guesstimate potential processing time improvement.

2

MySQL-8.0 has a number of ALTER TABLE variations in innodb than can be done online.

Recommend testing the table structure in a test environment using ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE; Do this one index at a time.

Other reference documentation has examples.

Alternatives include gh-ost which require more spac.e

2
  • thanks for this. Sounds like you'd propose adding indexes to the existing table as the most efficient (ie, quickest) approach generally? Maintanability seems to be the primary advantage to online DDL, but isn't of enormous concern in this case. Also, don't have the benefit of a test environment (working on a personal machine). Not sure if that changes your answer.
    – Chris
    Mar 12 '20 at 4:32
  • 1
    Its definitely the most efficient now that MySQL-8.0.X supports it. A test environment could just be CREATE TABLE test_XXX LIKE XXX and perform the alter table statements on the test_XXX table without data. This just ensures that the change can be done inplace without locks.
    – danblack
    Mar 12 '20 at 4:35

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.