I was working on a MySQL database using Innodb with billions of lines of data.
My table got 16 fields, 3 indexes, 1 foreign key, and 1 primary key. My buffer pool size is 47 GiB.
When I tested inserting 10 million lines with indexes disabled, it took 100s.
When indexes are enabled, as I was continuously inserting billions of lines in chunks of 10 million lines, it initially took 150s...then went up 400s...then to 1000s...and now it went up to 2000s per 10 million lines (using load data statement).
I want to know how to speed up my insertion with these indexes that are necessary for later use.
MySQL crashes a couple times during the insertion and I have to restart it...I wonder if the slow down is correlated to the crashes. Thank you!