MySQL: Adding indices after huge insert -> Faster or not

I've recently discussed the following scenario with a coworker:

(MySQL 5.x, InnoDB, single transaction)

• Table A 开发者_如何学Python20 mio Entries
• Table B Empty (has several indices on it)

Query: INSERT INTO B AS SELECT some_fields FROM A WHERE date < XY -- selects about 3/4, or 15 MIO elements

Now to our questions:

• Main Question: Would it be faster to first deactivate/remove all indices from Table B and then read them afterwards, or is this all the same?
• Bonus Question I: Any other ideas to improve this kind of query? it takes waaays to long IMHO.
• Bonus Question II: Write cache is deactivated on that disk, could that have a major impact (more than usual).

Thanks for your help!

---

You definitely want to do

ALTER TABLE B DISABLE KEYS

before running the insert. Otherwise MySQL tries to calculate after every insert. Once you've inserted everything:

ALTER TABLE B ENABLE KEYS

Once you do that MySQL will recalculate them all at once. It will still take some time but much less than doing it on each insert.

Improvements: Check that your date column on table A is indexed. It may be that your select is taking a long time if it's not.

I'm honestly not sure on the write cache question