I've set off index creation on a very large table in MySQL and while I expected it to take a long time, I'm 5 days in and wondering if there's any way to debug potential issues or simply let it run. I don't have a precise row count but to estimate, it's in the 100s of billions of rows and the table is ~400GB on disk. Neither memory or CPU usage appears to be overly taxed (mem ~8GB (out of 16GB total)).

The call I made from within MySQL is as follows:

alter table prices add index(dataDate, ticker, expDate, type), add index(s
ymbol), algorithm=inplace, lock=none;

Running show processlist from within a different MySQL instance shows the call with State 'altering table' so the call doesn't appear blocked. Anything else I can check to gauge progress?

For reference I'm working with MySQL 8 and within Ubuntu 18.04.

Edit: Indexes eventually completed, took a little over 9 days.

  • Eh? 100 billion rows will not fit in 400GB of disk space! Which number is wrong?
    – Rick James
    Commented Jun 3, 2020 at 0:06
  • What query do you think that index will help? Usually starting with a "date" is counter-productive.
    – Rick James
    Commented Jun 3, 2020 at 0:07
  • @RickJames, counterproductive even when dataDate will be used within where clauses?
    – Chris
    Commented Jun 3, 2020 at 2:47
  • 1
    WHERE x=1 AND date BETWEEN... works better with INDEX(x, date) than having the index in the opposite order. Even if x is just true/false.
    – Rick James
    Commented Jun 3, 2020 at 4:28
  • 1
    And... Here is my "proof": stackoverflow.com/questions/50239658/…
    – Rick James
    Commented Jun 3, 2020 at 4:30


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.