In my MySQL database, there is a table which has 2,000,000 records. Now, I would like to insert another 6,000,000 new records into this table.
To speed up the insertion, I though I should use disable/enable keys like following:
ALTER TABLE cars DISABLE KEYS;
INSERT INTO cars ...
...
...
INSERT INTO cars ...
ALTER TABLE search_all_values ENABLE KEYS;
OPTIMIZE TABLE cars;
But I somehow feel that, the disable/enable keys would make more sense to be used for empty table insertion.
While in my case, I have already 2,000,000 records in the table, when ENABLE KEYS, mysql will re-create all the indexes (including the existing records and new added records) which probably won't produce a efficient data insertion as a whole in my case. As re-create all the indexes will take long time and probably so does OPTIMIZE TABLE
I would like to ask your opinion about am I right and how can I have a efficent data insertion in my case?