I want to speed up my system a bit, and make some inserts happen in a one bigger insert. Currently I'm inserting some items into the db in a for-loop, but if I make a one sql-query out of the inserts and insert them at the end of the process, this would help me reduce the time that is needed to connect to the MySQL db, since there is no need to make a new connection for each item.
Problem: I need the auto increment id's from those inserts. Now I can get them with the lastInsertId() -function, but only when I insert them one by one.
Question1: Is it even possible to get all the AI-id's from a multiple row -insert?
Question2: If it's not possible to get the id's from a multi-row-insert, is it possible to keep the connection open as long as all the items has been saved?
Maybe1...: I thought that maybe I could get the last insert id of the multi-row-insert, and then calculate the other id's based on how many inserts I made. But I'm not sure how the mysql actually works. If there are many other connections, is there a possibility that the other connections get some of the AI values that should belong to our current connection? This would end up in a situation where I should have id's: 1, 2, 3, but other connection stole some of these and we end up having: 1, 3, 5, and thus, the calculating won't work. I'm not an expert with the MySQL, and I cannot find a decent explanation of this in the docs.
Maybe2...: I was also thinking that if all else fails, I could get the just inserted id's with a SELECT -query. This could be faster than the original way if there are more than 2 items to be inserted. Or could it? The math would be that I insert 3 items in a one query and get the just inserted id's with one, VS I insert 3 items in 3 queries. But when would this be meaningless optimization and when an actual time saving feature?
Conditions: I'm using Symfony2 -framework and Doctrine DBAL for the connections. One item has 20 columns worth of data. 1/3 of the columns are TINYINTs, 1/3 are VARCHARS of varying lengths but usually we are talking about short strings of 5-15 chars. And 1/3 of the columns are INT's and DOUBLE's that are small values as well. There can be anything of 1 to 60 items to be inserted at a time. I think about 90% of the inserts have around 1-6 items in a one session.
Update:
I've been testing this out and it seems that the DBAL doesn't close the connection but re-uses the already created connections by default. This is good. I also tested the inserting of multiple items in one query VS inserting them one at a time. Results are not what I expected.. It didn't matter how many items I inserted, but the time it took was roughly the same. This sparks up a question: why? One might think that 100 inserts individually is slower than one insert with 100 rows, but this didn't seem to be the case. Does anyone have any insights on this behaviour? Why doesn't it work like I thought it would?
