Inserting many rows.
Question:
1) Should I use set autocommit to OFF and use php to loop results un-buffered
$mysqli->query("MY QUERY", MYSQLI_USE_RESULT);
and build mutli insert statements.
2) Should (Or can I) set autocommit to OFF and do single query "insert into select"? Does autocommit to OFF work with a insert into select?
3) Export results to data file and than use load data outfile? I read somewhere that this has major benefits when inserting large set of data. However not every instance that my script runs will always have alot of data.
How many records are we talking?
Maybe 5 or maybe 100000. Depends on time of day and whats going on with software.
Thanks.
It depends on the size and your personal style.
I prefer to export/import the data, because:
you've one file, you're able to check, modify and move easily.
I'm used to it on large files.
It's common, I guess. I never used/learned a different way and nobody complained.
Unfortunately I've no benchmarks or stats but I think it's a fast way. I hope my personal opinion helps you to choose.