I’m in the process of converting an old MySQL database to a shiny new MySQL database controlled via Doctrine. Some of the tables have tens of thousands of records in them, so I was curious if it made sense to flush after each insert, wait till the end and flush or flush periodically during the process.
What I found was that waiting until the end to flush 200,000 records didn’t really work, it took forever and I never did see it complete. Flushing after every insert into the new DB was slow as a hell, but it did work. I came down to just flushing every 1,000 records and that seems to work great.
So in my PHP code that’s handling the conversion (Symfony Console Component), I run a counter and check $counter % 1000 == 0, I flush, then I flush when it’s all over to catch the stragglers.
If you’re using Symfony, don’t try to do this sort database conversion via a controller and a web page, it simply doesn’t work. Use Symfony’s Console Component bundle, it works great.