Willem Bogaerts wrote:
>>> INSERT INTO mytable(...) VALUES(...);
>>> SET @Id=LAST_INSERT_ID();
>>> UPDATE mytable SET col1=MOD(@Id, 30) WHERE id=@Id;
>>> -- More rows:
>>> INSERT INTO mytable(...) VALUES(@Id, ...);
>>>
>>> This should be done in one connection session, otherwise the variable
>>> @Id is cleared.
>> Yes, but it's less efficient than when updating multiple rows. Each
>> entry has an INSERT, a SET and an UPDATE. This requires three
>> statements to be parsed and executed for each insert - much slower,
>> unless you have a very slow database connection.
>>
>> My way has one INSERT per row, on get last insert id and a single
>> UPDATE. Parsing/execution will be much faster.
>
> The last INSERT can have more than one row and could even be delayed, I
> think. If the data to insert is not too complicated, you could even
> write a stored procedure for the above queries. I think the above
> queries are not more load than the "external" ID handling, but it can
> take some load of the webserver or the data traffic.
>
> Best regards

William,

I suggest you try it with a few thousand rows. I think you'll find
there is a significant difference in the processing time required.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================