Re: multiple current_timestamp

[Tobias Asplund]

On Sun, 29 Jul 2007, Jim Winstead wrote:

> I don't know if there is a strong technical reason for only allowing
> a single timestamp field to be automatically set or automatically updated,
   > or the limitation was just a carry-over from when only the first timestamp
   > field was auto-set/update. I wouldn't expect anyone at MySQL to jump at
   > implementing this, but a patch would be looked at. (But since you can get
                                                         > the same effect with
                                                       > a trigger, it may not
                                                       > have a shot.)

The effect can be the same, but the performance is far from there in a trigger
implementation.
We tested this for one of the User Conference sessions this year and found
that timestamp is quite efficient.

We tested:
1. Letting the timestamp take the value automatically
2. Having a BEFORE INSERT trigger set the column value into a DATETIME column
3. Forcing the function NOW() into a DATETIME column

http://www.flupps.org/pics/timestamp_benchmark.015.jpg

We found that if you use NEW. or OLD. in a trigger a second pass of 
"Opening tables" is executed, the high concurrency to the table_cache
slowed it down a bit in this case (at least, that's what we could 
see from limited testing under time strain).

-- 
Tobias "flupps" Asplund <tobias@stripped>
Trainer and Consultant, Sweden
MySQL AB   http://www.mysql.com