@lucidbrot is your sql file gzipped?

I found out I was doing the transaction at the wrong database(modified a script that had pulled it from another source) i was actually reading from, so quite useless, I modified the transaction to be done on the remote server connection. The writes themselves were quite fast in the 5000 batches, but pushing the data to the remote server still quite slow. I'm suspecting some kind of rate limiting being in the way.

I have everything wrapped within a laravel transaction, yet it seems as if it's still writing row by row. Not sure what could be the issue there, as I said, the same code locally goes blazingly fast. Maybe the transaction doesn't work on the remote? Disabling the cache didn't work. I tried with the mysql workbench migration wizard and it went up to 20mb/s so something is happening that makes the write go slow when accessed via php. Thanks for your insight.

I did some testing, keeping it into the original tables with indexes it performs in 0.014 milliseconds, if I use intermediate tables it runs up to 16 milliseconds :-)

So, to make actively use from the fact i'm already doing a lookup i'd better save the results of the lookup and use that temp table in the join? or is it better practise to depend on the query optimiser?