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.
Best Answer
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