Re: multiple current_timestamp

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 
Trainer and Consultant, Sweden
MySQL AB   
http://www.mysql.com

-- 
MySQL Internals Mailing List
For list archives: 
http://lists.mysql.com/internals
To unsubscribe:    
http://lists.mysql.com/internals?unsub=lists@pantek.com