Jump to content

Archived

This topic is now archived and is closed to further replies.

ToonMariner

Retrieving insert id

Recommended Posts

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.


Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
I thought there was a difference in when the indexes were updated... I'll have to profile it to double-check.

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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!!!!

Share this post


Link to post
Share on other sites
True... the easiest way is simply to add another column and mark it accordingly, and fetch the UIDs later.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.