I have the following pseudo code running on mysql 4.x:
insert ignore into dest_table
select t1.field1, t1.field2, t1.field3, t2.field1,
t1.field3, t2.ts, @cnt:=@cnt+1
from table1 as t1 left join table2 as t2 using (field1, field2)
order by t2.ts;
This works perfectly to sequentially number the result set rows
inserted into dest_table in order of t2.ts (a timestamp field).
In my upgrade to mysql 5.1.14-community, the numbers returned by @cnt
are not in order... they trend upward from 0 to the number of records
inserted, but they're far from "in order"... so somehow mysql is
inserting the rows in some strange order.
How can I fix this so it works in both mysql 4.x and 5.x?