Retrieving insert id

[ToonMariner]

Hi Guys 'n Gals...

Just a quicky hopefully.

I run an insert query like so..

[code]
INSERT INTO
`image`
(
`image_id` ,
`image_path` ,
`image_type` ,
`image_dimension` ,
`image_size` ,
`image_alt` ,
`image_title` ,
`image_caption` ,
`image_longdesc`
)
VALUES
(
NULL ,
'xxx
'xxx',
'xxx',
'xxx',
'xxx',
'xxx',
'xxx',
'xxx'
),
(
NULL ,
'xxx
'xxx',
'xxx',
'xxx',
'xxx',
'xxx',
'xxx',
'xxx'
),
(
NULL ,
'xxx
'xxx',
'xxx',
'xxx',
'xxx',
'xxx',
'xxx',
'xxx'
)
[/code]

It matters not about 'xxx' at the moment.  What I wish to achieve is to grab all the id's that query creates. mysql_insert_id returns the last but I want a list of them all...

Any help much appreciated.

[shoz]

I don't know of any way to get a list of ids created after using an INSERT with multiple VALUES.

Unless you're inserting a great deal of information at one time I don't think you'll notice much of a performance difference when using single INSERTs in a loop, using mysql_insert_id() to retrieve the ids versus one with multiple VALUES.

If you do notice a difference, post and let us know.

[fenway]

I thought there was a difference in when the indexes were updated... I'll have to profile it to double-check.

[shoz]

[quote author=fenway link=topic=107652.msg432195#msg432195 date=1157996589]
I thought there was a difference in when the indexes were updated... I'll have to profile it to double-check.
[/quote]
EDIT: Ignore the following comment.

There may be, but how do you determine whether the indexes that last got updated are from this connection doing an insert or another connection doing an insert.

Maybe an md5hash of all the info concatenated with microtime a random number and an incremented value inserted would work to keep track, but I don't think there's enough of a reason in this instance to try unorthodox methods.

[fenway]

Well, in a controlled environment, one would expect to see multi-valued INSERT statements scall well will a growing number of indexes, while individual INSERT statement should slow down noticeably.

[shoz]

Nevermind what I said. I misinterpreted what you said and only realized that after seeing that the misinterpretation didn't make sense either.

Hence, my own comment doesn't make sense.

The mysql docs do say that the multiple value insert is faster btw. Although, there was a time I tested it and noticed no real difference.That was some time ago and perhaps I made an error somewhere or perhaps used a bad test.

http://dev.mysql.com/doc/refman/4.1/en/insert-speed.html

[fenway]

Right, I forgot about that refman page.  The take-home messages from that page, IMHO:

1) You have to open the table once per statement, that doesn't scale well otherwise
2) You have to connect every time too -- also, bad for lots of statements
3) Turning off keys is good, less overhead
4) Locking the tables is also good, less overhead
5) Using LOAD DATA INFILE is _insanely_ faster -- personal experience on 50K bulk insert

[ToonMariner]

So how does all this help me grab the list of id's inserted?

I coudl concievabley have 50 record sets inserted with one query!!!!

[fenway]

True... the easiest way is simply to add another column and mark it accordingly, and fetch the UIDs later.